VLOOKUP?? employee schedule hours problem

[gavinbox]

Hello,

I've had help creating a decent rota for my staff for a small cafe.
I'm happy for people to use what I've got for themselves, so download the file and go for it.

What I'd love, is a separate tab whereby I can have a input a date range, and it tells me the hours the staff member has worked. I'd like column A to be data validated drop downs to choose the employee, then column B populates how many hours they've worked based on the date range selected.

Please help, this will make splitting tips and working out end of month hours a lot easier.

Gavin

https://www.dropbox.com/s/0tw1fit7z55l250/ROTA%202.0.xlsx?dl=0

 

[gebobs]

I'd like column A to be data validated drop downs to choose the employee

Normally, you could just specify a range on your main tab to populate the data validation dropdown. However, since each employee record occupies a merged cell in A this results in blanks between each name. Thus, you'll have to maintain a list of employees elsewhere, perhaps on its own tab.

then column B populates how many hours they've worked based on the date range selected.

How is the range specified?

At any rate, your formulas for calculating the total hours per week are overly complicated.

=SUM(MOD(AFT43-AFT42,1)+MOD(AFU43-AFU42,1)+MOD(AFV43-AFV42,1)+MOD(AFW43-AFW42,1)+MOD(AFX43-AFX42,1)+MOD(AFY43-AFY42,1)+MOD(AFZ43-AFZ42,1))*24

This can be simplified to;

=(SUM(AFT43:AFZ43)-SUM(AFT42:AFZ42))*24

These intervening totals and other columns where you list the names again are going to throw a wrench in the calculations you want. Without them, you could have a table with each employee and the months as the column headers and use sumproduct to test for the date being in each month and totaling the hours for those records that do.

The weekly totals that you already have in the main sheet could be calculated in the same fashion in another table just for weeks. Or it's possible that a pivot report could do what you want. But the current design handicaps any of these solutions. There's a lot going on in your sheet and I don't know how open you are to redesign, but that's my 2 pence.

 

[JeanRene]

Hi!

I took an interested in your problem, it was not easy but I believe I was able to work on a pretty decent solution. You can find the file here:
https://cloud.mail.ru/public/e033781ba390/ROTA 2_solution.xlsm

Hope this is what you needed!