Linking Tabs to Sum

[nattiej101]

Hello!  I'm not sure the best way to go about this project, so I was wondering if you all could help.

I am trying to total the hours of each pay code and labor cost for each day not worked.  Here is the data that I have that need to be linked:

In tab 1 (NPD) - I have the dates, and shops, and which days which shop didn't work

Tab 2 (CC) - these are the cost centers per shop.  The shops are on the top, and the cost centers in the rows

Tab 3 (All Data) - this has all of the dates of the month, the hours worked, cost centers, and labor codes.

Here is what I need to calculate with the three tabs:

If shop has an x on the date in tab 1, I need to calculate the hours worked per cost center.  So, for 5/13, I need to sum up all of the cost centers of ASSY N, ASSY MAT N, PAINT N, ect.  So, I would go to the CC tab and see all the cost centers associated with those shops (look at all the cost centers for ASSY N), then go to the All Data tab and total up all of the Hours for the different Pay Codes (so filter/sum if Cost Cr = TCN-cost center in question).

So - as a report out, I need all of this information in a pivot table: Cost Center, Date, Pay Code, Labor Class, Number of Hours summed.

Please help!!  Hope this helps and let me know if it doesn't make sense.  I've attached the document I am using

[#2]

I would suggest you to create what is called a UDF (User-defined-function) in VBA so you can use it in a formula like =FULLCOSTOF("ASSY N", 05/13/2022) and all the logic goes into VBA because of the complexity of it.

Once you have that formula working. You can create a regular table with all your shops and a period you want to analyze (say 05/13/2022- 11/25/2022), and use the formula to calculate the costs.

Once you have the costs populated into a column on that table you might create the pivot table for your report without problems.

If for some reason you believe it is too complex. I can definitely help you out with this. Here is my LinkedIn profile just in case

https://www.linkedin.com/in/custom-automations/

[#3]

@nattiej101 

My suggestion would be to solve the working hours with a SUMIFS() function.

=SUM(SUMIFS('All Data'!$G$2:$G$10600,'All Data'!$J$2:$J$10600,OFFSET(CC!$A$3,0,XMATCH($B3,CC!$A$2:$R$2)-1,44),'All Data'!$E$2:$E$10600,C$2))

Please check my example critically to see if it fits.

[#4]

Hello!! This looks like it should work for the totals - is there anyway to breakdown the Sum/Total by the Pay Code and Labor Costs on the All Data tab?

[#5]

@nattiej101 

That is certainly possible. It would be best if you prepare a table showing which evaluation you need. An example calculation with correct results that I can compare with would be helpful.

[#6]

@dscheikey 

I just modified and made a final output tab.... So I need to totals broken out by cost center, date, pay code, and labor costs.  I'm just not sure how to make it easier since this is a lot of data.  (This is only one month of one location.)

Thanks again for all of your help!

[#7]

@nattiej101 

I have created the SUMIFS formula for the table. Enclosed is the result.

I have also created a pivot table for you. With so many parameters, this seems more practical to me? Doesn't it? What was still missing was the shop name in the data table. For this I created another auxiliary column 'L' in All Data with a function that reads out the shop name via Cost Ctr over the table CC.

If you filter the table for a shop, not so many empty columns are displayed.

[#8]

This looks good - but I'm not sure how you created this? I have to be able to recreate this for other files as well. I see the formula for the Shop column you created. How did you create this pivot table?

[#9 · Accepted solution]

@nattiej101 

That is not so difficult. If you want to work with a pivot table in the future, you will need the formula in column L because the shop information can only be found in the CC. The formula I wrote will also work in any other of your worksheets as long as you have the table CC with the same range CC!$A$3:$R$46 of data. If necessary you have to adapt it.

If you have the data then you need to select the data range and then create the pivot table. Try that out. It is not difficult. I'm sure there are many good documentations on the internet and YouTube on how it works.

You still have to make some settings so that it looks exactly like in my example. But with a little trial and error, you're sure to get it right.

[#10]

@dscheikey I started working on this with all of the April data... and I can't get my Shop formula to work.  What did I do wrong?

[#11]

@nattiej101 

You must always fill the SEQUENCE() part with the number of columns in CC. In this case 103.

=LET(cn,SUM(MMULT(--(J2=CC!$A$3:$CY$60),SEQUENCE(103))),IF(cn=0,"without shop",INDEX(CC!$A$2:$CY$2,cn)))