Find all entries in a range and tally numbers based on codes in adjacent fields

Hi All

Hoping to get some help for this little issue. I am working on a rostering template for work and wanting to get some assistance with the final stage. The worksheet is as follows.

Worksheet "Week 1" has the following
The names and shift codes are in the range B1:W115
There is then a list of all staff names in Column X
Then a tally of the hours would be put into Column Y

Worksheet "Data" has the following
The list of shift codes in column J
The hours each shift code represents in column K

So the idea is to find all of the names (as they are entered) in range B1:W115 that are the same (so all the Freddy Mercury's for example) and then look at the shift codes in the cell next to them next to them (if the name was in A1 for example the shift code would be in cell B1 of the same worksheet). Compare those shift codes to those in column J of worksheet "Data" to find the hours in column K of worksheet "Data"
and then in column Y next to the person's name in column X add the hours up for all the entries on the worksheet.

Here is the file attached
Example template.xlsm

Regards

Rey2

Hi there
Yes! that is doing what I am after... Only question is there a way to get it to update automatically? I tried calling the macro on sheet change and it was throwing errors up "method 'find' of object 'range' failed as a run time error when placing it in the worksheet change property of the worksheet.

Regards

Rey2

I actually spoke a bit too soon ... The code works really well for the Monday to Friday entries but then doesn't see the Saturday or Sunday entries.

I added the Saturday and Sunday entries into the day array as such

Please Login or Register  to view this content.

however when I enter something into the sunday box I get an object required error number 424.

Thank you for all your help.

Rey

Thank you very much for all the help.

Sunday isn't working. And well this is waay beyond my abilities to figure out why...


Thanks for all the support!
Rey

I thought about that when I checked it out and to no avail. I thought it might have been where the table was located as well and so changed that as well. Still no luck ... I have attached the version that you uploaded with the only change being the data validation.

Once again thanks for the help and I am apparently not allowed to spread more reputation for your assistance.

Example template ver 3.xlsm

Thats what I thought would happen ... the curious thing is that sunday PM wont work. Ive done some cell merging etc that makes the saturday PM column staff name and shift codes the same (I and J). Saturday PM works correctly. Sunday doesn't

Example template ver 3b.xlsm

Thanks so much Leith

Sorry I didn't get back to you earlier ... got massively busy at work and I don't actually use a PC for my job that much but this is awesome!

Thanks heaps

Hi Leith

I have done a little creative editing to your code .. Basically commented out a couple of things to allow column B and C to be included and everthing still looks good. Those columns are included in the daily worksheet and there were evening shifts located under them so I changed their appearance and brought them into line with the other cells and how the macro is searching and finding for things.

but other wise Yes solved! Thanks heaps.

Rey

Yeah well it kind of did that a little but I am actually ok with reading through code and having it make sense, especially when the code has enough comments through it to help explain the functions. Basically just commented out the sections that were stuffing up and expanding the offset to include the desired columns. It still functions and is quite quick (when compared to the first iteration of your code) but does take a bit longer ... then again I am not the one using this all the time so yeah not too bad...

Just putting in some final touches (dynamic named range for the X column and then camera section for the other weeks in the worksheet so that when using the sheet you don't have to keep clicking back to week 1 to get the tally of hours) then theres a vlookup that I am planning on putting into another worksheet that will hopefully populate a roster as such for each person... we'll see how that goes.

Rey