Hiya peeps,
So I'm trying to work something out.
In column A I have staff names.
In row 1 I have the days of the week.
Column B is titled Monday
Column C is titled Tuesday and so on.
So say for example in cell B1, I have written down 88 (this means that staff 1, is working 8-8 on Monday. I've done a simple countif then multiple that result to work out how many hours I'm using that day.
Then it got more complicated as other shifts were introduced. 8-7, 8-6. 8-5 etc.
So that was quite easy to do. However now I need to encompass loads more variations. As if a nurse rings in sick and we get cover, but the cover doesn't come in until 10, it might get recorded as 108. (10-8).
The hours are recorded like this so it's easier for the head nurse and head carer to type in the shifts. It's how they've done it for years so I thought it'd be easier to adapt my idea to their way of inputting.
Anyways, I've now come up with loads of different variations. 76 to be exact. So apart from having a really large formula in each total box at the bottom which counts each variation and then multiples it by the number for hours worked that it represents, is there some way I could do this by getting it to look-up this information from a table?
So say on another sheet, I've listed all the different variations and next to it listed how many hours they mean. So the total box on the original sheet checks how many of x there are then multiples it by the vlookup value in the table.
I hope this makes sense.
Cheers in advance for any help.
So I'm trying to work something out.
In column A I have staff names.
In row 1 I have the days of the week.
Column B is titled Monday
Column C is titled Tuesday and so on.
So say for example in cell B1, I have written down 88 (this means that staff 1, is working 8-8 on Monday. I've done a simple countif then multiple that result to work out how many hours I'm using that day.
Then it got more complicated as other shifts were introduced. 8-7, 8-6. 8-5 etc.
So that was quite easy to do. However now I need to encompass loads more variations. As if a nurse rings in sick and we get cover, but the cover doesn't come in until 10, it might get recorded as 108. (10-8).
The hours are recorded like this so it's easier for the head nurse and head carer to type in the shifts. It's how they've done it for years so I thought it'd be easier to adapt my idea to their way of inputting.
Anyways, I've now come up with loads of different variations. 76 to be exact. So apart from having a really large formula in each total box at the bottom which counts each variation and then multiples it by the number for hours worked that it represents, is there some way I could do this by getting it to look-up this information from a table?
So say on another sheet, I've listed all the different variations and next to it listed how many hours they mean. So the total box on the original sheet checks how many of x there are then multiples it by the vlookup value in the table.
I hope this makes sense.
Cheers in advance for any help.
