G
Greg
All,
I have a VBA-formula hybrid workbook that is used to track employees'
vacation time. It is calendar-style, with the days of the month 1-3x
running from left to right, months running vertically. For a day off,
you enter in the code for the type of vacation and the number of hours
in the cell below. Each employee has their own sheet and each
department has their own workbook.
I was recently asked to add a 'team calendar' sheet that would
consolidate all employees' time off onto one page so the manager could
see if there were vacation conflicts.
(VBA-approach) I first tried to accomplish this by having a For/Next
loop circle through all appropriate cells per employee sheet and
export to the Team Calendar tab. This, unfortunately, took a lot of
time to do and was not very efficient. For reference, 5 employees
could take up to 15-20 seconds.
(Formula-approach) My second attempt consisted of an INDIRECT formula
referencing each cell on each sheet that could have a value. The
problem here is that every time a sheet is modified, the auto-
calculation slows down the sheet dramatically!
I hope I have provided enough information and look forward to any
suggestions.
Thank you
I have a VBA-formula hybrid workbook that is used to track employees'
vacation time. It is calendar-style, with the days of the month 1-3x
running from left to right, months running vertically. For a day off,
you enter in the code for the type of vacation and the number of hours
in the cell below. Each employee has their own sheet and each
department has their own workbook.
I was recently asked to add a 'team calendar' sheet that would
consolidate all employees' time off onto one page so the manager could
see if there were vacation conflicts.
(VBA-approach) I first tried to accomplish this by having a For/Next
loop circle through all appropriate cells per employee sheet and
export to the Team Calendar tab. This, unfortunately, took a lot of
time to do and was not very efficient. For reference, 5 employees
could take up to 15-20 seconds.
(Formula-approach) My second attempt consisted of an INDIRECT formula
referencing each cell on each sheet that could have a value. The
problem here is that every time a sheet is modified, the auto-
calculation slows down the sheet dramatically!
I hope I have provided enough information and look forward to any
suggestions.
Thank you