Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    HolidayCalendar (2003 SP2)

    Good morning

    I am seeking some assistance in further modifying the attached workbook, on my working copy I have made adaptations to suit suit some my needs and can cope up to 100 employee's if needs be. I am stuck however on the following;

    a. Is it possible and if so how, can I use column D to validate the appropriiate cells with a type of absence

    b. I have tried to add a COUNTIF function in Column AK (this is based at the moment on there being only one possible selection and the calendar cells are coloured according to a True or False calculation), I have tried =COUNTIF(F5:AJ5,"True")

    c. If the formula I referred to in (a) above can be modified, could I then have different COUNTIF's in columns AK, AL, AM etc. to count the different types of ansences.

    Although I have modified this workbook it is not mine but I found it with out an authors name so if it was originally yours, thank you for the starter

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: HolidayCalendar (2003 SP2)

    I am a little confused, you already have "Validation" in column D.

    Are you looking for something like the attached?
    I added a range (LeaveTypes, =AL3:AR3) which is the allowed list. It is used as a lookup and the value is also used for the countifs in the column. Can be hidden
    I added a range (Holidays, =AU5:AU16) can be hidden and/or moved

    I added a column E (named sType) which looks up the type and gives it a number (so the sumpropduct can calculate it) This number is then used get the type of leave to display.

    A null designates a weekend, an "H" a holiday. I used the same Grey for them, teh Holiday displays the "H"

    I changed the conditional formatting to match the new values. The "blank cells" have a #Value error since it tries to lookup the zero value from the sumproduct, the other values give the letter.

    Steve
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayCalendar (2003 SP2)

    Steve, thanks

    To say I am over the moon is an understatement, can I beg a small mod though because I cannot see how you have done it. Public Holidays are not taken of the annual leave at our company, how would I change those cells from H to say PC (Public Holiday) so that actual holiday (vacation, leave or whatever) is green like all of the other absences and the COUNTIF would then presumably only count those H's so that I can subtract them from the allowance in another sheet on my main workbook. I did read your notes but they are a little (lot!!) over my knowledge thresh hold

    The validation I had in D on the original was only to illustrate what I was after and did not affect anything when selected,

    Thanks once again
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: HolidayCalendar (2003 SP2)

    In all the formulas change the "H" to "PC":
    For example from:
    =IF(WEEKDAY(G$4,2)>5,"",IF(ISNUMBER(MATCH(G$4,Holi days,0)),"<font color=red>H</font color=red>",INDEX(LeaveTypes,SUMPRODUCT(($F5=SName s)*(SFrom<=G$4)*(STo>=G$4)*SType))))
    to:
    =IF(WEEKDAY(G$4,2)>5,"",IF(ISNUMBER(MATCH(G$4,Holi days,0)),"<font color=red>PC</font color=red>",INDEX(LeaveTypes,SUMPRODUCT(($F5=SName s)*(SFrom<=G$4)*(STo>=G$4)*SType))))

    The change the formula fo the 2nd conditional formatting from:
    =OR(G5="",G5="<font color=red>H</font color=red>")
    to:
    =OR(G5="",G5="<font color=red>PC</font color=red>")

    Now the items explicitly given as "H" in the list, will be green, but the "global PCs" will be grey.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayCalendar (2003 SP2)

    Thanks for the prompt response Steve

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: HolidayCalendar (2003 SP2)

    A few comments (ask any questions if you need more details or elaboration):

    You had asked about "validation" in Col D, which you already were doing. Validation is just that: "validating the data entry". Using the result of validation is no different than using the results from any other cells. Using the result of a validated cell in a formula is not "validation".

    The calculation in the month cells give various results based on the IF formula and lookup:
    1) if the date is a weekend, it puts in a null string ("")
    2) if the date is a public holiday (from the holidays list) then it is marked with a "PC". These are dates that everyone gets off so they are like "weekends". If there are no paid holidays, the list can be left blank or this part of the IF may be removed.
    3) If the date is not a weekend or a holiday, then the date is not "global" (for everyone) but is examined for the individual. This uses the SUMPRODUCT like you had originally. I expanded it to not just get the "count" (your "1" or "0") but multiplied it by the MATCH of the "type". If a person has no listings in A652 for that date it gives a zero (0) if it is found it uses the match number in E (which is derived from the data validated number in D and the "LeaveTypes" range). INDEX is used to lookup the MATCH in the list. If the SUMPRODUCT gives a zero the INDEX will give a #VALUE error, otherwise it will give the "LeaveType".

    Thus from the above the value in the cells will be a null (Weekend), a "PC" (in the public Holiday list), a #VALUE error (name and date has not been entered, this is the mahority of the responses) or a letter from the "LeaveTypes" list.

    The conditional formatting is setup that if the value is the error that it is "Hidden" by making the text white. If a null or a PC, these are days that "globally paid off" and are colored grey. Any other days are individually "off-days" and are marked green.

    I hope this clears it up a little.

    Steve

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayCalendar (2003 SP2)

    Hi Steve

    Sorry but I am stuck again, I have tried repeatedly to get your examples to work in my working copy but It just does not seem to perform as per your example, for some reason I could not shrink a workbook to less than 110kb even with only 1 row of data but I am hoping that with your experienced eye you can see what ever stupid mistake I am making from this limited sample.

    Thank you

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: HolidayCalendar (2003 SP2)

    I see 2 problems:
    1) You do not have a formula in E6:E328. E6 needs:
    =IF(D6="",0,MATCH(D6,LeaveTypes,0))

    Copy/Autofill it down the column

    2) since you use sumproduct all the named ranges must be the same size.
    Use as the "refers to" for the named formula "SType":
    ='2008'!$E$6:$E$328

    to match the other ranges. it currently goes to row 780 so the sumproduct gives an error.

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayCalendar (2003 SP2)

    Thanks Steve

    <font color=blue> "1) You do not have a formula in E6:E328. E6 needs: =IF(D6="",0,MATCH(D6,LeaveTypes,0)) </font color=blue>

    I don't know how that disapppeared in the zipped copy? row 7 does have the formula and it is in the original workbook all the way down to E780

    <font color=blue> "2) since you use sumproduct all the named ranges must be the same size.
    Use as the "refers to" for the named formula "SType":
    ='2008'!$E$6:$E$328

    to match the other ranges. it currently goes to row 780 so the sumproduct gives an error."
    </font color=blue>

    In my original workbook if I click on Stypes in the 'Name Box' it highlights row E6:E780?

    <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Sorry to be a bother

    Steve




    I don't kn
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: HolidayCalendar (2003 SP2)

    The "780" is not the issue for Stypes. In the original you posted the ranges all went to 52 which was what I used. in your original what are the ranges for STo, SFrom, SNames? It is all a matter of all of the names being the same size or sumproduct will give an error.

    You do not need to apologize, I am glad that I could help.

    Is it working now?

    Steve

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayCalendar (2003 SP2)

    Thanks Steve

    Having reset all of the range names it works great, as an aside I found a government website that listed all English Public Holidays until 2065 in an Excel worksheet(not that I will be around to see it and probably Excel will not be either) so I just copied it into my range with the result that holidays and other absences can be entered from Jan 2008 - Dec 2065 and when ever you change the year in G1 it shows any future booked days.

    I suppose though that this will add to the slowness of the workbook, out of interest how much faster does VBA make a workbook when it has to crunch through this volume of cells?

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: HolidayCalendar (2003 SP2)

    Do you notice a marked decrease in performance? If so, you could limit the holiday list to, say, the period 2008-2012. Surely you'll have other reasons to update the workbook within the next five years, so you'll have an opportunity to update the holiday list when needed.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: HolidayCalendar (2003 SP2)

    VBA most likely would be markedly slower if it had to crunch through all the same cells as the formula after every change to the worksheet. Excel's routines are written to minimize the unneccessary recalculations and its builtin routines with the formulas are much more efficient than VBA.

    There could be ways to minimize the recalculation of the sheet and to also eliminate some of the array formulas. A better methodolgy with code would be to clear all the months, then loop through the input list and set the colors/code, then loop through the holiday list and then the weekends. This methodology should not only be more efficient processing but would allow for separate colors for the type if desired.

    It could be triggered by a button, by the worksheet change event, or if the holiday list and the the input list were on different sheets (than the calendar sheet) it could be set to not run as often by attaching it to the worksheet activate event of the calendar sheet. Then it would only be run once, when all the changes were made. It would not be "live" like the formulas are, but would not need to be. It would be updated whenever you activated the calendar sheet. It most likely is not needed at any other time. This should eliminate any "sluggishness" from too many array formulas and also would still allow UNDOing (which is not possible if you would use a macro linked to the worksheet change event)

    Steve

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: HolidayCalendar (2003 SP2)

    If the length of the "holiday list" causes "sluggisness", you could try to fix it by using OFFSET to name a subset of the entire range, based on the year of the calendar instead of having to lookup in the entire holidaylist. You could also limit the size of the "S"-named ranges to what has been entered instead of using a set range when most are empty...

    Steve

  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: HolidayCalendar (2003 SP2)

    Thanks Hans

    That made quite a difference, I was just being flash really by adding them (you know, kid with new toy etc. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> )

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •