|Subject:||Vlookup or something more? Trying to make the book intuitive...|
|Date:||Thu, 10 Jul 2008|
I hope you can help. Iâ€™m trying to make my life much easier by making a
currently unwieldy spreadsheet into one thatâ€™s a lot more intuitive.
Currently I have to enter data twice into the same spreadsheet but I hope to
be able to enter it once and have the other sheets automatically update.
First, the explanationâ€¦ This is a spreadsheet that tracks the holidays of a
department of 20 people. Currently it fulfils a few functions. First, it
tracks each individual on their own worksheet which goes into details (date
began/ended, etc). Second, it tracks them all together on an overall
worksheet that just gives an overview (# of days) with a worksheet that is
linked from all the individual worksheets. Third, it has a sheet that is
sent to our HR department monthly. Currently I enter information into the
individual sheets and duplicate the info into the HR sheet.
The HR department sheet has all the info that is needed to fill in the
individual worksheets (which would then, as I have it currently set up, fill
in the overall worksheet). Currently I enter the same information into both
the HR sheet and the individual sheets. I just need to find something that
will transfer the overall information from the HR sheet to the individual
sheets or vice versa. However, keep in mind that the HR sheet will change
every month as it only reflects the data for that given month whereas the
rest of the sheets are meant to be cumulative for the year. One method would
be to have the HR sheet feed off the individual sheets as opposed to the
oppositeâ€¦ However, everybody doesnâ€™t necessarily take leave in a given month
and how would you set Excel up to monitor all 20 individual sheets for a
particular month value (e.g. January) in either A15:E15 or H15:K15 in and
then return a value using vlookup or something.
The HR sheet has the following info:
Initials, Staff Number, First name, Surname, Type of Leave, No of days
taken, Start Date, AM/PM, End Date, AM/PM
Each Sheet is named after the individual and has the following info:
1. Name of individual in cell A1
2. Carry forward leave in cell B5 with title in A5
3. Annual leave in cell B6 with title in A6
4. Total in cell B7
5. Summary of each type of leave between (titles) D1:D8 and (data) E1:E8
6. Summary of each month total in (titles) H1:H12 and (data) I1:I12
7. Holiday (title â€œHoliday Leaveâ€ in B13) leave detailed in cells:
7a. (titles: Month, No of days, Start Date, End Date, Leave Type) A15:E15
7b. (data) A:E starting from line 16 and will have as many lines as they
have episodes of leave.
8. Other Leave (title â€œOther Leaveâ€ in H13) detailed in cells:
8a. (titles: No of Days, Start Date, End Date, Leave Type) H15:K15
8b. (data) H:K starting from line 16 and will have as many lines as they
have episodes of leave.
Thanks a million in advance for all your help! If you need a visual let me
know and I'll send you an annonymised screenshot.