Looking for a formula to list staff names from shift paterns to days working

Hi,
I am creating a roster for a manufacturing company that changes from small scale operation to large scale operation twice a year. I have about 40 staff that are on a fixed 5 day on 2 day off roster, but the difference in staffing numbers between small scale and large scale mean I'd like a formula that takes the names from the shift pattern they work (ie Mon-Fri, Thur to Mon) and populate this in a column that lists the people who work a particular day.

I know I can do this with VBA but our company doesn't allow us to use them.

Please see attached for an example of what I'm after. I've populated the Monday to Friday people and filled in everyone who works Monday as an example

I am sure that this can be done more simply, but I can't see how!!

I added some numbers in row 2 to indicate the days encompassed by your range (Monday = 1). The formula is horrible... but it does (seem to) work!!

Formula:

Please Login or Register  to view this content.

A little polite feedback would not go amiss. However, even in its absence... You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[QUOTE=Glenn Kennedy;5431881]A little polite feedback would not go amiss. However, even in its absence... You're welcome. /quote]

I do apologise, I thought I had replied.
I had tried it out and it does work. Thank you so much.

Edit: can’t seem to do the reputation thing on the Phone. Will need to do that bit tomorrow. Sorry about that one.