Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Formula assistance (Excel 2000)

    Hi there

    I am back again with my timesheet. This has been working very well in my workplace, however, some brainwave decided it would be good to keep staff leave information on it. So now I have it calculating, but I need a summary page with references to all the dates and hours of leave in rows 19, 20 and 21. The fortnightly sheets are created by running the macro on the master sheet.

    I hope this is enough explanation. I will be glad to elaborate if anyone is interested in helping.

    Kerry
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula assistance (Excel 2000)

    Hi Kerry,
    Bet you thought this one didn't get a guernsey!
    Check out the attached.
    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Formula assistance (Excel 2000)

    Yep, I sure did! After I logged the question I thought I was fairly vague, so I probably didnt deserve an answer. So I am grateful of your reply. I would definately never have been able to put together a formula like this!

    It almost does the trick, just two little tweaks would make it perfect.

    Firstly, If you run the macro on the Master Sheet "Press here to set up for 12 Months", you will see that a sheet is made for each fornight in the following twelve month period. The template is then hidden and not used after setting up. So the reference to 'template' in the formula, needs to some how be changed to refer to all worksheets.

    Secondly, only the dates where there IS actually some leave taken needs to show up on the leave summary and then a total at the bottom of each column.

    Now.... you are probably regretting sifting through the archives finding my question with no replies :-) !!

    Kerry
    South Australia

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formula assistance (Excel 2000)

    Hi Kerry,

    Your response raises a few issues that make a purely formula-based solution difficult, if not impossible, to implement.

    Firstly, if the formulae need to refer to each of a dozen worksheets, they can't really do so until the worksheets have been created. So you'd need to use a macro to generate the formulae for the summary sheet once the others have been created.

    Secondly, even if the first problem is overcome, you'd still have a situation in which the formulae would have been created on the various rows before you knew when the leave was going to be taken. So all rows would be there regardless. I suppose this too could be overcome by the use of an event-driven macro to hide the unused rows (which is easy enough), but by then one may as well use such a macro to create the summary on the fly. Of course, then you'd have to allow for users editing their leave records too ...

    Given the above, I think it might be best to create a 'summarise leave' macro that simply goes through each of the sheets and creates the summary on demand. I'm not that proficient with VBA, so maybe you or one of the other loungers would like to have a go at this. Either way, the formulae I used should help with coding the macro to do something similar for each 'found' leave record.

    One other thing you need to consider: What if someone uses more than one type of leave on a given date?

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Formula assistance (Excel 2000)

    Hmmmm....yes, you raise some very valid questions. Me and my grandious ideas!! The idea of a macro run on demand in the leave summary sounds sensible, however, I too am unable to create such a thing.

    As for the two types of leave on one day!!! oh my god. I never thought of that.

    Well, if anyone is willing to have a go at this, then that would be wonderful. Otherwise I will have to go back to the drawing board.

    Either way, the problem isn't life threatening and I appreciate having the chance to bounce my ideas of around.

    Thanks

    Kerry

Posting Permissions

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