Some time ago I asked about how count text values in a rota to enable counting the number of staff on duty.
Thank you for the assistance as I am now using COUNTIF with good results. My new question is this.
Having a table with a column with staff names. Then a series of date columns below which the shift value is
listed. If I have a number of separate work books or work sheets (these contain similar data for different
departments with identical formatting) and wish to extract data on a daily basis so as to show who would be on
duty. How would I go about this?? The aim is to provide a single sheet with four separate areas as follows
Each area would contain the names of those on duty, their grade and gender. The data for each department would
also be separated within the column for day shift, night shift and training.
Below an example of two sets of data. Would be on separate work sheets. Under date blank = not working.
Department One ..........|....|....|...¬...Department Two
.........................|.18.|.19.|...¬............................|.18.|.19.|
Name....|.Grade.|.Gender.|.S..|.S..|...¬...Name....|.Grade.|.Gender.|.S..|.S..|
Paul....|.DCN...|.Male...|.D..|.D..|...¬...Lucy....|.SN....|.Female.|.N..|.N..|
Peter...|.SN....|.Male...|.N..|.N..|.......Anne....|.SN....|.Female.|.D..|.D..|
John....|.SN....|.Male...|.D..|....|...¬.......................................
........|.......|........|....|....|...........................................
Shirl...|.HCW...|.Female.|....|.N..|...¬...Ruth....|.HCW...|.Female.|.D..|.D..|
Helen...|.HCW...|.Female.|.T..|.D..|...¬...Mike....|.HCW...|.Male...|.N..|....|
Jean | HCW | Female | N | N | ¬
Earl | HCW | Male | D | D |
Aim is to have one work sheet per day to display four sets of data as below. Two sets of data shown the other
two would be listed similarly. Day and date to display just once.
.......................................................................
Saturday...............................18th September 2010
______________________................._____________________
Department One.........................Department Two
______________________................._____________________
Name..|.Grade.|.Gender.................Name...|.Grade.|.Gender
Day....................................Day....................
Paul..|.DCN...|.Male...|...............Anne...|.SN....|.Female
John..|.SN....|.Male...|...............Ruth...|.HCW...|.Female
Earl..|.HCW...|.Male...|.......................................
.......................|.......................................
Night..................|...............Night...................
Peter.|.SN....|.Male...|...............Lucy...|.SN....|.Female
Jean..|.HCW...|.Female.|...............Mike...|.HCW...|.Male
Training................................Training
Helen.|.HCW...|.Female.| |
.....................
Am hoping the above displays sufficiently to see what am getting at. A bit lopsided due to my inability to format in html.
Thank you for the assistance as I am now using COUNTIF with good results. My new question is this.
Having a table with a column with staff names. Then a series of date columns below which the shift value is
listed. If I have a number of separate work books or work sheets (these contain similar data for different
departments with identical formatting) and wish to extract data on a daily basis so as to show who would be on
duty. How would I go about this?? The aim is to provide a single sheet with four separate areas as follows
Each area would contain the names of those on duty, their grade and gender. The data for each department would
also be separated within the column for day shift, night shift and training.
Below an example of two sets of data. Would be on separate work sheets. Under date blank = not working.
Department One ..........|....|....|...¬...Department Two
.........................|.18.|.19.|...¬............................|.18.|.19.|
Name....|.Grade.|.Gender.|.S..|.S..|...¬...Name....|.Grade.|.Gender.|.S..|.S..|
Paul....|.DCN...|.Male...|.D..|.D..|...¬...Lucy....|.SN....|.Female.|.N..|.N..|
Peter...|.SN....|.Male...|.N..|.N..|.......Anne....|.SN....|.Female.|.D..|.D..|
John....|.SN....|.Male...|.D..|....|...¬.......................................
........|.......|........|....|....|...........................................
Shirl...|.HCW...|.Female.|....|.N..|...¬...Ruth....|.HCW...|.Female.|.D..|.D..|
Helen...|.HCW...|.Female.|.T..|.D..|...¬...Mike....|.HCW...|.Male...|.N..|....|
Jean | HCW | Female | N | N | ¬
Earl | HCW | Male | D | D |
Aim is to have one work sheet per day to display four sets of data as below. Two sets of data shown the other
two would be listed similarly. Day and date to display just once.
.......................................................................
Saturday...............................18th September 2010
______________________................._____________________
Department One.........................Department Two
______________________................._____________________
Name..|.Grade.|.Gender.................Name...|.Grade.|.Gender
Day....................................Day....................
Paul..|.DCN...|.Male...|...............Anne...|.SN....|.Female
John..|.SN....|.Male...|...............Ruth...|.HCW...|.Female
Earl..|.HCW...|.Male...|.......................................
.......................|.......................................
Night..................|...............Night...................
Peter.|.SN....|.Male...|...............Lucy...|.SN....|.Female
Jean..|.HCW...|.Female.|...............Mike...|.HCW...|.Male
Training................................Training
Helen.|.HCW...|.Female.| |
.....................
Am hoping the above displays sufficiently to see what am getting at. A bit lopsided due to my inability to format in html.
