Formula assistance (Excel 2000)

Topic

New Reply

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

Reply | Quote

Replies

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

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote

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
[Fmr MS MVP - Word]

Reply | Quote

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

Reply | Quote