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

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) 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.
0
moily (55)
7/10/2008 10:07:01 AM
excel 39879 articles. 2 followers. Follow

7 Replies
700 Views

Similar Articles

[PageSpeed] 27

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

On Jul 10, 11:07=A0am, Moily <Mo...@discussions.microsoft.com> wrote:
> Hi there,
>
> I hope you can help. =A0I=92m trying to make my life much easier by makin=
g a
> currently unwieldy spreadsheet into one that=92s a lot more intuitive. =
=A0
> 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=85 This is a spreadsheet that tracks the holidays =
of a
> department of 20 people. =A0Currently it fulfils a few functions. =A0Firs=
t, it
> tracks each individual on their own worksheet which goes into details (da=
te
> began/ended, etc). =A0Second, it tracks them all together on an overall
> worksheet that just gives an overview (# of days) with a worksheet that i=
s
> linked from all the individual worksheets. =A0Third, it has a sheet that =
is
> sent to our HR department monthly. =A0Currently 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, f=
ill
> in the overall worksheet). =A0Currently I enter the same information into=
 both
> the HR sheet and the individual sheets. =A0I just need to find something =
that
> will transfer the overall information from the HR sheet to the individual
> sheets or vice versa. =A0However, keep in mind that the HR sheet will cha=
nge
> 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. =A0One method=
 would
> be to have the HR sheet feed off the individual sheets as opposed to the
> opposite=85 However, everybody doesn=92t necessarily take leave in a give=
n 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 =93Holiday Leave=94 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 =93Other Leave=94 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! =A0If you need a visual le=
t me
> know and I'll send you an annonymised screenshot.

0
pashurst (2576)
7/10/2008 10:47:10 AM
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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Moily" <Moily@discussions.microsoft.com> wrote in message 
news:B986EE64-8B75-4DC4-A448-283F5DC5CD42@microsoft.com...
> 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) 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.
> 


0
sandymann2 (1054)
7/10/2008 11:08:30 AM
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  



"Sandy Mann" wrote:

> 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
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "Moily" <Moily@discussions.microsoft.com> wrote in message 
> news:B986EE64-8B75-4DC4-A448-283F5DC5CD42@microsoft.com...
> > 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) 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.
> > 
> 
> 
> 
0
moily (55)
7/10/2008 11:31:00 AM
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

"Pete_UK" wrote:

> 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
> 
> On Jul 10, 11:07 am, Moily <Mo...@discussions.microsoft.com> wrote:
> > 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) 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.
> 
> 
0
moily (55)
7/10/2008 12:03:01 PM
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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Moily" <Moily@discussions.microsoft.com> wrote in message
news:8223C163-4050-4FEA-8AB4-D9444AB4567E@microsoft.com...
> 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
>
>
>
> "Sandy Mann" wrote:
>
>> 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
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Moily" <Moily@discussions.microsoft.com> wrote in message
>> news:B986EE64-8B75-4DC4-A448-283F5DC5CD42@microsoft.com...
>> > 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) 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.
>> >
>>
>>
>>
>



0
sandymann2 (1054)
7/10/2008 9:41:19 PM
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" wrote:

> 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
> 
> sandymann2@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
> 
> 
> "Moily" <Moily@discussions.microsoft.com> wrote in message
> news:8223C163-4050-4FEA-8AB4-D9444AB4567E@microsoft.com...
> > 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
> >
> >
> >
> > "Sandy Mann" wrote:
> >
> >> 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
> >>
> >> sandymann2@mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "Moily" <Moily@discussions.microsoft.com> wrote in message
> >> news:B986EE64-8B75-4DC4-A448-283F5DC5CD42@microsoft.com...
> >> > 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) 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.
> >> >
> >>
> >>
> >>
> >
> 
> 
> 
> 
0
moily (55)
7/11/2008 8:29:02 AM
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

sandymann2@mailinator.com
Replace @mailinator.com with @tiscali.co.uk


"Moily" <Moily@discussions.microsoft.com> wrote in message 
news:71B21115-2F46-43FE-A65E-C4655D71D68C@microsoft.com...
> 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" wrote:
>
>> 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
>>
>> sandymann2@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Moily" <Moily@discussions.microsoft.com> wrote in message
>> news:8223C163-4050-4FEA-8AB4-D9444AB4567E@microsoft.com...
>> > 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
>> >
>> >
>> >
>> > "Sandy Mann" wrote:
>> >
>> >> 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
>> >>
>> >> sandymann2@mailinator.com
>> >> Replace @mailinator.com with @tiscali.co.uk
>> >>
>> >>
>> >> "Moily" <Moily@discussions.microsoft.com> wrote in message
>> >> news:B986EE64-8B75-4DC4-A448-283F5DC5CD42@microsoft.com...
>> >> > 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) 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.
>> >> >
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>>
> 


0
sandymann2 (1054)
7/11/2008 11:42:00 AM
Reply:

Similar Artilces:

using a date in vlookup
i want to perform a vlookup using the Now() function to generate the lookup value (A1), the 1st column in the table [col B] array will be all the dates in a year listed consequtive,, and the 2nd column being a value assigned to each day in the 1st column [B]. My formula is vlookup(A1,B1:C367,2). The result I get is "#N/A. What am i doing wrong? Thanks Tonso NOW() returns both the date and the time, so you would be better off using TODAY(), which only returns the date. Another problem might be that your dates in column B are really text values that just look like dates - see what happe...

Outlook 2003 & address book
Just switched from Outlook Express to Outlook 2003. When I click on Address Book I get the message "No entries in this address book." When I follow the instructions for transfering Contacts to Address Book I finally get this message: "This account or directory type already exists & cannot be used twice." How can I get Contacts into Address Book so I can use auto address. Thank you GHM GHM <mclou@hal-pc.org> wrote: > Just switched from Outlook Express to Outlook 2003. When I > click on Address Book I get the message "No entries in > this ad...

vlookup excel and access...
assuming i have this code, is possible to use this vlookup withnthe adta into mdb access?... old scenario: Private Sub TextBox25_Change() Dim CODICE As Integer Select Case Me.TextBox25 Case "" Me.TextBox4 = "" Case 1 To 8 CODICE = Val(Me.TextBox25) Me.TextBox4 = Application.WorksheetFunction.VLookup _ (CODICE, Worksheets("TABELLA").Range("Q2:R9"), 2, False) Case Else Call MULTI_LINE_BOX End Select End Sub new scenario: Inested column Q and R in excel i have created a mdb into: \\my server\myserverdir\USER.MDB and into this mdb have inserte a table U...

Vlookup within a vlookup
I am trying to lookup a cell within a table - but the table to use is found in another table. =VLOOKUP(B3,VLOOKUP(B2,F3:G9,2,2),2) Cell B2 is a dropdown box allowing one of the choices in colum f below. Column G represents which table to use for the initial lookup based on your choice in the drop down dox. column f column g Alt A 30 Yr fixed30 Alt A 15 Yr fixed15 All I get is an error - can someone help ? Thanks, Yosef It sounds like you would need to use INDIRECT within the lookup formula http://tinyurl.com/czxtt that thread shows the way to do it exce...

Making a Booklet .....
I havent worked in Publisher in a few months. I am putting together a booklet of 4 pages, all 11 x 17 paper. I am wanting to print, center fold and staple my booklet. However, I tried adjusting the paper to 11x17, tabloid, fold then staple. What I am trying to do is to print the publication, however, each page is printing twice. Page 1 is side by side, page 2 is side by side, etc. I just need one 8x11 page on one side and a different page on the right. I've asked this question a while back, but cannot figure out what I need to adjust. Is it the settings on the color printer? Karen...

Personal Address book in OWA
How do you configure OWA so that users are able to pull their Personal Address books. I noticed the Global Address list and Contacts when using the drop down menu, but cannot find out where to include a users' Personal Address Book. Do I have to create separate Address Lists in System Manager for EX2K3? Can somebody give me the steps to do it? Thanks in advance, Bill You can't. OWA can't access .pst files. You would need to have them put their personal contacts in the Exchange store, perhaps create a separate contact folder and call it "personal" "Bkana"...

Lost on Vlookup, match, etc....
Can someone walk me through this please? I a workbook that imports a years worth of data from filemaker to be analyzed and charted in excel. It contains several sheets, but I am concerned with worksheet 1 (daily data) and worksheet 2 (bodyweight). Daily data contains the raw data I pull in from Filemaker. It results in a table with a row for each day of the year. It has 12 columns, but in this instance, I am only interested in 2 of the colums Column F, (Date), and Column R (Bodyweight). There is only one entry per week for body weight. The bodyweight sheet has 3 columns (week #, date, and w...

VLOOKUP in VBA
On the worksheet I can insert in a cell =VLOOKUP(C5, Hobokee.xls!AcsLow, 2) and it works perfectly, looks up the value in column 2 of the range named AcsLow in the same workbook. But elswhere I want in a macro to lookup the same table and assign the result to a variable BalAmt. BalAmt = VLOOKUP(AccNum, Hobokee.xls!AcsLow, 2) does not work. It gives a function not defined error on Hobokee. If instead of Hobokee I put Workbooks("Hobokee"), it gives function not defined error for VLOOKUP which it changes to VLookup. I have been overVLOOKUP in the Help file and see nothing wrong. W...

Windows mail does not even try to use the SMTP server
Hi, I am using Windows Mail with Windows Vista. Someday, eevery thing start to work bad. My account are well configured (sure of that, it worked on some other computers) but Windows mail does not even try to connect to the SMTP Server. The Pop3 is working. The status pop do nov even show the SMTP Connexion as a task, so there is not error !!! I did many antivirus check and nothing. Thanks for your help Regards, F. What happens to messages you attempt to send? Do they stay in the Outbox? Your antivirus may be the problem. Which antivirus are you running? Some of them are not very...

VLOOKUP problem
I down a list of top 100 stock symbols to Excel and place them in Column A and their rank in Column B (1-100). the next week I download a new list of the top 100 stock symbols and their rank to Column C and D respectively. then I create column E using the function =VLOOKUP(C4,A:B,2,FALSE) Which tels me the rank each stock had last week and if a stock is new this week it puts "#N/A" in the appropriate cell in column E. How do I get the Vlookup function to put "NEW" in column E if the is new to the list and wasn't in the list last week? Ed =if(isn...

Making 2 colums into one
I have an excel file that I imported into Access. There are 5 fields. Item 1-5 The item names are in column 1 and column 3. I need to combine the two columns in to just one column. Can this be done? Thanks for your help and reply. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201006/1 Use a calculated field -- Combined: [column 1] & " - " & [column 3] Or omit the dash and use one space or comma and a space as you wish it to be displayed. You could add a new field in your table and run an...

Excel stops responding while trying to save or save as
While trying to save or save as, we are able to get to my computer and to the C drive. But when we attempt to open a folder in the C Drive excel locks up and we are forced to reboot. Has anyone else had this problem or know of a solution? any suggestions are greatly appreciated. ...

Making a worksheet and a graph that doesn't show zero values
Hi I am trying to build a template worksheet for my research. I get a file of logged data per day. I want to be able to copy the logged data from a .CSV file to a template file. Then separate worksheets will look at the raw data and import data from certain columns into that worksheet. The data is not logged in a regular way, there can be 30 cells between logging intervals or 29 or 31 cells. I can select that every cell in a column of Worksheet 1 looks at a set cell in the "Raw Data" worksheet. This will then give me a column consisting of logged data values with zeroes ...

I am trying to change the text size of the folder list in Outlook
not the preview pane, the actual folder list in the Inbox Window. It's so tiny. "Bayoubelle" <Bayoubelle@discussions.microsoft.com> wrote in message news:D431653F-FA94-4AD2-9668-380846EE6CB2@microsoft.com... > not the preview pane, the actual folder list in the Inbox Window. It's so > tiny. Always state your Outlook version. Click View>Arrange By>Current View>Customize Current View>Other Settings. Change the Column Font and Row Font settings to suit. If you're using Outlook 2007, then it's View>Current View>... I...

VLOOKUP #40
Would like to post data from one worksheet to another. Could you provide a few more details about what exactly you want to do. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "dr" <drivera@opvista.com> wrote in message news:1f1001c52c08$ad4c33a0$a601280a@phx.gbl... > Would like to post data from one worksheet to another. ...

import Address book from outlook express
I am installing Outlook 2000 and tried to import address book from my current outlook express. I have subfolders with addresses in them in express all listed under "Bill's Contacts" When importing, addresses in Bill's contacts moved under contacts in Outlook but subfolders under Bill's contacts as well as information in subfolders did not move. The wab address book I have in express is titled Bill.wa~ and is found in folders: windows/ applications/ microsoft/ address book. I am trying to import these contacts and the sub-folders in express into outlook 2000. ...

combobox and vlookups?
I am trying to add a combobox that when you select from the dropdown menu, the columns nextdoor automatically pull up corresponding data that is related to the selection from the dropdown list. Does this make sense? What do I do to set this whole thing up? (I don't know code). Thanks. -- Message posted via http://www.officekb.com You shouldn't need code for anything you described here. Set up the combobox with the list fill range, if any. Set the linked cell to the desired cell. Then, use the VLOOKUP command in another cell to look for the linked cell from the combo box to lo...

how to make command button not visible.
I have a form which has a command button on it that I want to make invisible. the code is as below. Private Sub cmdUseJoBlocks_Click() ' some other useless code here. cmdUseJoBlocks.visible = False End Sub the problem is that when I run this code it errors out saying " Run-time error '2165': You can't hide a control that has the focus." any help would be greatly appreciated. The problem is straight forward. You can't use code behind a control to hide the control itself. You have to put the code somewhere else, but since I don&...

VLOOKUP
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01CAC8EE.B1306170 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I use the VLOOKUP function to pull basic data from external data sheets, currently an example of my command looks like this: =VLOOKUP($A5,'[RT NP 67 MF.xls]ODD'!$A$1:$S$250,$L$1,FALSE) The "$AA%" is the data I am looking up and the "$L$1" is a variable to the column I am wishing to insert. The question I am trying to get an answer for concerns...

vlookup inside an if statement?
-------------------------------------------------------------------------------- Hi everybody! I have to do a vertical lookup of an object that might or might not exist, in case it doesnt exist normally it gives back Ref! but the thing is that if this objet doesnt appear on the data base is because is zero. This Ref! is not allowing me to make a sum since this objet is part of it. Maybe with an example would be easier to explain. i have to pull from a database the number of cars sold of three brands A,B, C, In the database unless is one car of the type it will not appear. lets say we hav...

something about clipping in OnCustomdraw
hi folks;-) the following snipped comes from a customdraw-fn in a slider. As you can see, i draw my own thumb and want to set some text in the control: ____________________________________________________________ class SHTC_API CQSlider : public CSliderCtrl { public: CQSlider(); virtual ~CQSlider(); // ... other stuff ... // // set sliders text, may contain '%d' (or %x...) for value void QTitleSet(LPCTSTR szTitle); protected: //{{AFX_MSG(CQSlider) afx_msg void OnCustomdraw(NMHDR* pNMHDR, LRESULT* pResult); //}}AFX_MSG DECLARE_MESSAGE_MAP() private: void _DrawThumb(NM...

How do I make an average on a report out of formula totals?
I've made a report titled HR Efficiency Report. This report contains the employee name, their pay rate and all of the information needed to calculate formulas. I have successfully created a text box titled Total Eff that properly displays the formula =[On Std Earned]/[Total Hourly Pay]. This report is based on a query and I would like the user to be able to run the query based on a single employee (which I have successfully done as well). However, I am running into a problem when I try to get an average for my Total Eff text box. Every time I try to open it it opens an Enter Parame...

trying to get active view pointer from cmainframe
hi group, i am programming a mdi application. in which i want the active view's pointer. so after searching i got this code on msdn. void CMainFrame::StepLine(CString Line) { //pView->HighLightLine(Line); CMDIFrameWnd *pFrame = (CMDIFrameWnd*)AfxGetApp()->m_pMainWnd; if(pFrame) { CMDIChildWnd *pChild = (CMDIChildWnd *) pFrame->MDIGetActive();//here it gives //assertion as given below if(pChild) { CTritonSpiritBoardView *pView = (CTritonSpiritBoardView *) pChild->GetActiveView(); } } } Debug assertion failed! Program: File:winmdi.cp...

incorporate paste value in vlookup
is there a way to incorporate "paste value" in a vlookup formula ? I have the vlookups in the column working and feeding off of the multiple worksheets. the data will be updated weekly (and new columns with the updated data added) and i would rather just copy new data on top of the old in the worksheets instean of having to add new worksheets with new data... as to keep the previous column from changing. -- problem ...

Yes you can get your 20 free e-books
Many e-books for free 20 Free E-Books : About home business, internet marketing, personal growth and many more subjects intend to help you to grow your business on the internet. Just send a blank e-mail at clbooks@freeautobot.com ...