I have been asked to sort out some stuff on our rota at work, it is done in Excel.
How can I 'flag up' / conditional format so that it shows that someone cannot undertake a task that day as they are on day off / hols / at other centre / on expedition.
So column 1 is date, column 2 is 'On Duty' then a long list of names across the top row. I need to flag up that if the name in Column 2 co-incides with 'hols' on that day, below that name it produces an error/red etc.
Any helpers?
Conditional formatting
You'll need to build a nested Vlookup & Hlookup function into the formula field of the conditional formatting. It's bit convoluted to be honest.
Cheers, will go google n play for v and hlookup
Are you typing 'Holiday' in a cell or looking for a formula to put in each cell that will populate 'Holiday' automatically based on another list of data (names and hol dates) that you have?
hlookup will work but as stumpyjon said - it could get convoluted. You can help yourselft a bit by sticking in two hidden columns say at 3 and 4 just to spread it out a bit. Column 4 just number 1 to 31 to say which row hlookup is picking up from (index). Column 3 put the hlookup stuff in so you can see what it is pulling out. The conditionally format col 2 based on what is in 3.
....then hope that it all works
There are other ways but that should work
There are other ways
Such as...?
How do you know at the moment if someone is working on holiday on a particular day? Is this in a seperate list? What version of excel are you using?
Few questions, about the layout, first column is 365 rows ie one cell per day for whole year? Column two is the one i am having issues with, why a column for on duty and then row headers for names?
I would have the first row as you have for days, then x columns each headed with an employees name. Each cell is then list validated as work or holiday.
This is the issue, why complicate it anymore?
Such as...?
The problem with hlookup used in the way that I described before is that it relies both on a column of numbers and the names being spelled correctly. Get either wrong and it doesn't work but you might not see that it isn't working.
You can get rid of the column of numbers by using MATCH and then INDEX to check for holidays but you could still spell the names wrong and it won't work
A better way is to rearrange how you do it. Rather than writing the name of the person on duty in the column on the left, write 'duty' in the column of the person who is on duty (the same column where you are writing holiday I believe). Then in the left hand column pull out the name of the person who is on duty using MATCH and INDEX. Then you can't spell the name wrongly
For example, if the names are in row C1:H1 and you have named that range 'People' then stick this formula in B2 and then copy it into the cells below
=INDEX(People,1,MATCH("duty",C2:H2,0))
maybe
Problem with that is the cell that is under their name, say for Wednesday this week shows (for example) 'I' for instructing or 'C' for centre etc.
matt - can you describe your lay out a bit better as its unclear.
#its very easy to do a conditional format for what youre trygin to do. give me a better idea of layout and Ill put the formula up here.