A formular to look up data and return dates
 

I keep a spread sheet running that has dates along the top for each month and
names of staff down the side, if they take a holiday or are sick we enter "H"
or "S"
What i would like to have is a summery sheet that will list actual days
taken as sick or Holiday. So i assume maybe some sort of Lookup at looks for
the Letter H or S and then checks the date and then the name and returns a
date.
Is this possible?

Many thanks in advance for any help

A formular to look up data and return dates
 

Hi Louis,

To keep it simple you'd need to use the COUNTIF function.

e.g.

With dates in row 1 starting at column A and staff names in column A
starting at row 1.
Enter the formulae =COUNTIF(B2:D2,"H") into cell E2 and
=COUNTIF(B2:D2,"S") into F2.
Copy these formulas down for the other staff members.
Blank cells and any cells containing anything other the H or S are
ignored.

14/12 15/12 16/12 Holidays Sick Days
Rob h s s 1 2
Mary h h 2 0
Kevin 0 0

NB The range B2:D2 works only for this example and will need to be
expanded for your version.

Hope this helps.
JF.

louiscourtney wrote:
I keep a spread sheet running that has dates along the top for each month and
names of staff down the side, if they take a holiday or are sick we enter "H"
or "S"
What i would like to have is a summery sheet that will list actual days
taken as sick or Holiday. So i assume maybe some sort of Lookup at looks for
the Letter H or S and then checks the date and then the name and returns a
date.
Is this possible?

Many thanks in advance for any help

A formular to look up data and return dates
 

JF thank you for replying
i have that all ready but what i would like is something that lists on a
seperate sheet that rob has had a holiday or sick on such and such day and
the it lists it something like this
Holiday
Rob 14/12,
In the row of Rob there would need to be space for 20 entrys so i could tell
when he has completed his hol intitlement and on what days he took them ,
without having to go back though each week / month
Would be fantastic if you could solve this for me as it is doing my head
in!!!!

" wrote:

Hi Louis,

To keep it simple you'd need to use the COUNTIF function.

e.g.

With dates in row 1 starting at column A and staff names in column A
starting at row 1.
Enter the formulae =COUNTIF(B2:D2,"H") into cell E2 and
=COUNTIF(B2:D2,"S") into F2.
Copy these formulas down for the other staff members.
Blank cells and any cells containing anything other the H or S are
ignored.

14/12 15/12 16/12 Holidays Sick Days
Rob h s s 1 2
Mary h h 2 0
Kevin 0 0

NB The range B2:D2 works only for this example and will need to be
expanded for your version.

Hope this helps.
JF.

louiscourtney wrote:
I keep a spread sheet running that has dates along the top for each month and
names of staff down the side, if they take a holiday or are sick we enter "H"
or "S"
What i would like to have is a summery sheet that will list actual days
taken as sick or Holiday. So i assume maybe some sort of Lookup at looks for
the Letter H or S and then checks the date and then the name and returns a
date.
Is this possible?

Many thanks in advance for any help

A formular to look up data and return dates
 

Here's one set-up which should give you the required results ..

Assume source data is entered in a sheet: X,
dates in B1 across, names listed in A2 down to say A10

Put in say, B12:
=IF(B2="","",IF(B2="H",COLUMN(),""))
Copy B12 down to B20, then copy across as far as required to cover the max
expected extent of source data. Leave A12:A20 blank.

Then in a new sheet named as say: Holidays
with the same names pasted in A2:A10

Put in B2:
=IF(COLUMN(A1)COUNT(X!12:12),"",INDEX(X!$1:$1,SMA LL(X!12:12,COLUMN(A1))))
Format B2 as date. Copy B2 down to B10, then copy across to the same extent
as done in X. This will return the required results for "H".

Extend the construct similarly to retrieve corresponding results for "S" in
another sheet, viz:

In sheet: X,
Placed in B22:
=IF(B2="","",IF(B2="S",COLUMN(),""))
B22 is copied down to B30, then copied across as far as required

In a new sheet: Sick,
Names pasted in A2:A10
Placed in B2:
=IF(COLUMN(A1)COUNT(X!22:22),"",INDEX(X!$1:$1,SMA LL(X!22:22,COLUMN(A1))))
Format B2 as date. Copy B2 down to B10, then copy across to the same extent
as done in X. This will return the required results for "S".
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"louiscourtney" wrote:
... what i would like is something that lists on a
seperate sheet that rob has had a holiday or sick
on such and such day and it lists something like this
Holiday
Rob 14/12,
In the row of Rob there would need to be space for 20 entrys
so i could tell when he has completed his hol entitlement
and on what days he took them ,
without having to go back though each week / month

A formular to look up data and return dates
 

Max
I can get part of the formula to work but not the rest
What I've got is a sheet with the following
Names in A4 to A98
I then have two other columns with there personal data and then i have
columns D4 to AH4 down to D98 to AH98 which is enoungh space to add "H" for
each day of the week in say January.
I then what a summery sheet call holidays that will list all the names down
in A4 to A98 and some sort of calulation that looks at the january tab and
wioll only return if they have had a holiday and return the date in as many
cells that they have had holiday.
Thank you so much for your help so far

"Max" wrote:

Here's one set-up which should give you the required results ..

Assume source data is entered in a sheet: X,
dates in B1 across, names listed in A2 down to say A10

Put in say, B12:
=IF(B2="","",IF(B2="H",COLUMN(),""))
Copy B12 down to B20, then copy across as far as required to cover the max
expected extent of source data. Leave A12:A20 blank.

Then in a new sheet named as say: Holidays
with the same names pasted in A2:A10

Put in B2:
=IF(COLUMN(A1)COUNT(X!12:12),"",INDEX(X!$1:$1,SMA LL(X!12:12,COLUMN(A1))))
Format B2 as date. Copy B2 down to B10, then copy across to the same extent
as done in X. This will return the required results for "H".

Extend the construct similarly to retrieve corresponding results for "S" in
another sheet, viz:

In sheet: X,
Placed in B22:
=IF(B2="","",IF(B2="S",COLUMN(),""))
B22 is copied down to B30, then copied across as far as required

In a new sheet: Sick,
Names pasted in A2:A10
Placed in B2:
=IF(COLUMN(A1)COUNT(X!22:22),"",INDEX(X!$1:$1,SMA LL(X!22:22,COLUMN(A1))))
Format B2 as date. Copy B2 down to B10, then copy across to the same extent
as done in X. This will return the required results for "S".
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"louiscourtney" wrote:
... what i would like is something that lists on a
seperate sheet that rob has had a holiday or sick
on such and such day and it lists something like this
Holiday
Rob 14/12,
In the row of Rob there would need to be space for 20 entrys
so i could tell when he has completed his hol entitlement
and on what days he took them ,
without having to go back though each week / month

A formular to look up data and return dates
 

Yup, that's exactly what the earlier set up delivers

Ok, to adapt it to suit your actual layout,
try this on a spare copy ..

Re-name the source sheet as simply: X

In X,
Dates are assumed to run in D3 across to AH3

Put in D100:
=IF(D4="","",IF(D4="H",COLUMN(),""))
Copy D100 down to D194 to cover the 95 names,
then copy across as far as required to AH194.
Leave A100:C194 blank.

Then in a new sheet named as: Holidays
with the same names pasted in A4:A98

Put in D4:
=IF(COLUMN(A1)COUNT(X!100:100),"",INDEX(X!$3:$3,S MALL(X!100:100,COLUMN(A1))))
Format D4 as date. Copy D4 down to D98, then copy across to the same extent
as done in X, viz to AH98. This will return the required results for "H",
with all dates neatly bunched to the left.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"louiscourtney" wrote:
Max
I can get part of the formula to work but not the rest
What I've got is a sheet with the following
Names in A4 to A98
I then have two other columns with there personal data and then i have
columns D4 to AH4 down to D98 to AH98 which is enoungh space to add "H" for
each day of the week in say January.
I then what a summery sheet call holidays that will list all the names down
in A4 to A98 and some sort of calulation that looks at the january tab and
wioll only return if they have had a holiday and return the date in as many
cells that they have had holiday.
Thank you so much for your help so far

A formular to look up data and return dates
 

Max
Thats absolutley perfect
Just one final thing
How do i go about adding all the months(which are on seperate tabs) to the
holiday sheet you have created
All the sheets have been set up exactly as the one we have just worked on
Looking forward to your response

Shane

"Max" wrote:

Yup, that's exactly what the earlier set up delivers

Ok, to adapt it to suit your actual layout,
try this on a spare copy ..

Re-name the source sheet as simply: X

In X,
Dates are assumed to run in D3 across to AH3

Put in D100:
=IF(D4="","",IF(D4="H",COLUMN(),""))
Copy D100 down to D194 to cover the 95 names,
then copy across as far as required to AH194.
Leave A100:C194 blank.

Then in a new sheet named as: Holidays
with the same names pasted in A4:A98

Put in D4:
=IF(COLUMN(A1)COUNT(X!100:100),"",INDEX(X!$3:$3,S MALL(X!100:100,COLUMN(A1))))
Format D4 as date. Copy D4 down to D98, then copy across to the same extent
as done in X, viz to AH98. This will return the required results for "H",
with all dates neatly bunched to the left.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"louiscourtney" wrote:
Max
I can get part of the formula to work but not the rest
What I've got is a sheet with the following
Names in A4 to A98
I then have two other columns with there personal data and then i have
columns D4 to AH4 down to D98 to AH98 which is enoungh space to add "H" for
each day of the week in say January.
I then what a summery sheet call holidays that will list all the names down
in A4 to A98 and some sort of calulation that looks at the january tab and
wioll only return if they have had a holiday and return the date in as many
cells that they have had holiday.
Thank you so much for your help so far

A formular to look up data and return dates
 

Best to consider using just 2 source sheets for the entire year, each sheet
to house 6 months worth of dates (~180 days) from col D to col GA (Copy n
paste the col data sequentially from the 6 source monthly sheets). Then just
extend the formula fills in rows 100 to 194 to suit. Then use 2 destination
sheets, eg: Hols_1, Hols_2 to extract the results
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"louiscourtney" wrote in message
...
Max
Thats absolutley perfect
Just one final thing
How do i go about adding all the months(which are on seperate tabs) to the
holiday sheet you have created
All the sheets have been set up exactly as the one we have just worked on
Looking forward to your response

Shane