Hi
I use Excel to write the rota for my workplace (an intensive care unit), with each row being one persons rota. The data is either a "D" (day shift) a "N" (night shift) or an empty cell (day off). This all works very nicely, but it would be really useful if I were able to export that data to a series of other documents/sheets to form lists of who is working on a particular day. For example:
May
-------- 1 ---- 2 ---- 3 ---- 4 ---- 5
Dave --N------N--- -N-----------
Mike --D------D--- ---------D------D
Phil ---D------D---- --------N------N
Cath ---------D---- -D------D-------
Carl ---N--- -N---- -N--------------
This data would then be used to form 5 printed lists (one for each day on the rota) with the 1st one reading:
1st May
Day Shift :
Mike
Phil
Night Shift:
Dave
Carl
Basically the crux of what I need to do is create a list in one workbook "Staff" that draws data from another workbook "Rota". Each name in the list is entered in a new cell.
Basically everyone with a "D" in a cell on a particular day in "rota" will get entered on that days "staff" list. Filling in the 1st cell in the list in "Staff" is straightforward, but I am just using formulas, and am not sure how to make the cell below know that the 1st name in the list has already been entered, and to move on to the next name.
I cannot just have a list formatted the same (ie same number of cells) as the original workbook as with 70 staff this would have too many gaps and not look neat printed.
I suspect a macro may be required which is beyond me. Thanks.
David
Bookmarks