Creating a list from a roster

Hiya! I hope I can explain my needs well enough!

I have two worksheets, one which contains a roster, the other of which contains allocation sheets for the fortnight's staffing. I want to take the roster (which contains a list of staff and which shift they are working each day), and use that data to create an allocation sheet.

The way I am doing it now works PERFECTLY for my computer, but when the person who does the rosters uses the sheet on a mac or an older pc, it creates huge problems (puts the wrong people in the wrong place, throws tonnes of errors). They are not particularly computer-literate and there isn't much flexibility in layout (stuck in the past, etc etc).

I am hoping someone can present me with some very simplified option to do what I want to do that I just haven't thought of yet. I am hoping that I am doing it in a needlessly complex way - I use excel only when I need to and tend to go looking for functions that may let me do what I want to do.

roster1.jpg

Above is the roster, or a small part of it atleast. It shows a fortnight of rostering across the top. For each day there are two columns - the first is for shift-specific information (E=Early, L=Late, DO=Day off, CM=Case management). The right column is for other information that I don't need to parse for rostering/allocations (requested, leave, sick, etc).

roster2.jpg

This table parses the roster into day-by-day lists of who is working and on what shift. As I said, I put it together fairly haphazardly a fair while back, so I hope I can construe what it does, but it looks for people with an "E" in the shift column (people who are working an early shift), and gets their name to put on the list, working down the column. I then use this data to parse into a sheet for allocations which is a pretty simple process of referencing the correct cell from this table.


Essentially, I want to know if there is a more simple way of parsing the roster into the allocation table, as shown above. If anyone has any ideas, please let me know. In my mind, what I need to do is create an array of all the cells containing "E" within a given column, then create a matching array of all the names from column A that match those cells.

Any ideas with this? I have attached a version of the roster with all information stripped (the names are from a fake name generator to make the sheet "easier" to visualise).

Basically, the sheet "allocation" takes data from the other 3 sheets to create its output. I am looking for a simplified, backward-compatible way of doing this. At the moment, it uses needlessly complex formulas, and in the case of the day roster I create the data in advance before I pull it to make it easier to fix :s The "calculations" for the day roster are over to the right of the day roster, starting from column AG.

Basically, if you know of a way I can turn the Day Roster as it is into the allocation sheet as it is, that would be fantastic!

Helps if I actually attach the file. . .

Testsheet.xls