Vlookup or something more? Trying to make the book intuitive...

[Moily]

Hi there,

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) D18 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.

 

[Pete_UK]

I think it would be better to have the HR sheet take values from the
individual sheets. You will need a cell on the HR sheet to hold the
month/year for that return, and then formulae elsewhere will be able
to pick up data from each person's sheet which matches the month/year.
If you have a leave period that spans two months, eg 25th May to 6th
June, then you might need to enter this as two separate periods in the
individual sheets, eg 25th May to 31st May and 1st June to 6th June,
to keep things simpler.

You could allocate, say, 10 rows for each person on the HR sheet, as
no-one is likely to have this many separate periods of leave in one
month, and then use a filter to hide the rows that are not filled for
that month, before sending the sheet off to HR.

I've done a similar thing for someone through the newsgroups where
they wanted to track sales each month for salesmen who each had a
separate sheet.

Hope this helps.

Pete

 

[Sandy Mann]

If your HR department is anything like the one we had when I was working
then they will want the data in consecutive lines with no gaps. To do that
I think that you will need to write a Macro to take the copy data from the
20 sheets to the HR sheet. The example that you give is confusing, (at
least to me), because, for example you say:

The HR sheet has the following info:
Initials, Staff Number, First name, Surname,

But give simply:

1. Name of individual in cell A1

Where would the rest of the data come from?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Moily]

Hi Sandy,

Thanks so much for responding!

That's so true... and they also don't get the entire spreadsheet - just the
one tab so I have to paste special values into a new spreadsheet. To be
fair, it wouldn't take much longer to remove the extra lines but your method
might be a little cleaner.

HR uploads the data into their own database (not sure if it's Access or
other software) so they need the info separated into: Staff Number, First
Name, Surname, Type of Leave, No of Days, Start Date, AM/PM, End Date, AM/PM.
I currently use formula in several of those columns to intuitively fill in
others. ie I fill in the initials of the staff member and the first name,
surname and staff number all fill in. Also I enter the number of days away,
the start date and start time (AM/PM) and the end date and time fill in.
This is done through workday formulas but - as a side note - it doesn't help
with when we have public holidays which I then need to manually adjust.

All of the data should come from each individual's sheet but I currently
hard code it twice. Once in the individual's sheet and once in the HR sheet.
The workday formulas are also in the individual's sheet. I have the HR
sheet currently set up as above so only some of the info would have to come
from the individual's sheets but it would be helpful if the HR sheet is not
made up of formulas at all - instead is just a dump of data in the right
order so I don't have to paste special even. Does this help or have I made
it even more confusing?

I've noticed your email and am sending you an annonymised copy in case that
would help and you're willing.

Best,
Ann

 

[Moily]

Hiya Pete,

Thanks a million for responding but it would make the spreadsheet a bit
clunky - especially since HR needs to upload the data into their own database
(not sure which software program they use). Also, I already mess around with
the data before I send the tab to them by paste specialling only the values
but - and I know I'm asking to be spoiled rotten - but I'm looking for Excel
to find the data and do a data dump - almost a report I guess on a monthly
basis. Maybe a better choice would be to go with Access but it's always been
in Excel and I was hoping to see if it could be done...

Best,
Ann

 

[Sandy Mann]

I am not the best programmer in the world but the following Macro copies the
selected month's Holidays to the HR sheet. To get it to work I had to add
some formulas to your Sheets:

1. In each person's Holiday sheet in Cell C62 insert the formula:

=SUBTOTAL(2,C16:C61)

to count the number of dates showing when an autofilter is applied.

2. Copy and paste this formula to Cell I62

=SUBTOTAL(2,I16:I61)

3. In Cell F16 enter the formula:

=IF(C16="","",MONTH(C16))

and copy down to F61

4. Similarly in L16:L61 enter the formula:

=IF(I16="","",MONTH(I16))

If you Group the sheets you can enter these formulas in all the people's
sheets at the same time.

Finally in Cell B1 for each Person's Holiday sheet enter their initials

Now copy and pase this Macro into a normal Module:

Option Explicit
Sub MonthHolidays()
Dim c As Worksheet
Dim MonthNo As String
Dim LastHr As Long
Dim LastInitial As Long
Dim Plus As Integer

'Select HR Sheet
Sheets("HR").Select
Application.ScreenUpdating = False

'Clear Old data in HR Sheet
Range(Cells(2, 1), Cells(61, 1)).ClearContents
Range(Cells(2, 5), Cells(61, 7)).ClearContents
Pick:
'Get month number to copt to HR sheet
MonthNo = InputBox("Month Number?", "Anne's Macro")
If Not IsNumeric(MonthNo) Then GoTo Pick
If MonthNo = "" Then GoTo Pick
If MonthNo < 1 Or _
MonthNo > 12 Then GoTo Pick

'Loop through Staff Holiday sheets
For Each c In Worksheets
If c.Range("A1").Value = "Initials" Or _
c.Range("A1").Value = "" Then GoTo skipIt

'Copy Holiday (0) and then other (6)
For Plus = 0 To 6 Step 6

'Autofilter & copy for selected month
With c.Range(c.Cells(15, 2 + Plus), c.Cells(61, 6 + Plus))

.AutoFilter Field:=5, Criteria1:=MonthNo

If c.Cells(62, 3 + Plus).Value = 0 Then GoTo NoData

c.Range(c.Cells(16, 2 + Plus), c.Cells(61, 3 + Plus)).Copy

LastHr = Sheets("HR").Cells(Rows.Count, 6).End(xlUp).Row

Sheets("HR").Cells(LastHr + 1, 6).PasteSpecial _
Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

c.Range(c.Cells(16, 5 + Plus), c.Cells(61, 5 + Plus)).Copy

Sheets("HR").Cells(LastHr + 1, 5).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

LastHr = Cells(Rows.Count, 6).End(xlUp).Row
LastInitial = Cells(Rows.Count, 1).End(xlUp).Row

Range(Cells(LastInitial + 1, 1), Cells(LastHr, 1)).Value = _
c.Cells(1, 2)
NoData:
.AutoFilter
Application.CutCopyMode = False

End With
Next Plus

skipIt:
Next c

Application.ScreenUpdating = True
End Sub

The Macro clears all data in Columns A, E, F & G then autofilters each
Holiday sheet in turn and copies the month's Holidays to Columns E, F & G
and the person's initials to Column A.

Note that your *Leave Type* in the Holiday Sheets do not match the *Leave
Code Legend*

Also note that you can hide the error codes that you age getting by, for
example in B2 making the formula:

=IF(A2="","",VLOOKUP(A2,$P$2:$S$22,4,FALSE))


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Moily]

This is brilliant Sandy - I'll try it out today! Thank you so much!!!!! You
don't know how much double checking time you've saved me!!!

 

[Sandy Mann]

Your are very welcome.

I forgot to point out that I use the fact that only the sheets that you want
to copy data from, (and the HR sheet), have anything in Cell A1. If you
want to use the top visible row in other sheets then add a new Row 1 and
then hide it.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk