I'm having to rework our staff rota due to the limitation of nested IF statements in Excel
So I now have 4 worksheets.
Worksheet 4 just has a named range of available studios
Worksheet 2 lists each member of staff in a single row which is split into 3 columns per day of the week(AM, PM, Evening). In each cell there is a drop down list which is populated by the resources above in Worksheet 4. So you would then go to each member of staff and select the studio they will be working in for each part of the day 6 days a week.
When a studio is selected in Worksheet 2 next to a staff members name it then enters that numeric value into the appropriate cell in Worksheet 3 so if Studio 2 is selected it enters a 2, Studio 4 gives a 4 and so on.
I now want to create a 5th and final sheet which produces a view of all the studios and who is in them i.e. columns across the top for AM, PM & Evening for 6 days and rows for each of the studios.
In the cells I want to write a formula that cross references worksheet 4 and 2 so that the staff members name now appears in the relevant studio on the correct days and times but I'm not sure how.
I'm pretty sure it's a case of using VLOOKUP but can't quite get my head around it.
