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