Cannot use Pivot Table - I think I need sumifs

[learningexcel2020]

I have attached a spreadsheet of what i have been trying to do for weeks. Management will NOT let me use pivot tables. I am trying to calculate from a date range, the customer name, they type of labor they reported for the week (according to what project they worked on) then sum the total of hours.
I have a large amount of data i will enter in one spreadsheet - I want the formulas in another sheet, pulling the data from the download, in a neat way management can read it.

I would really, really, really appreciate some hlep. I have tried and tried and i have come up with 'date value' 'index' 'sumifs' but i can't find a way to get everything together.

Thanking you in advance.

 

[RoryA]

Out of interest, why won't they let you use pivot tables?

 

[learningexcel2020]

Management does not like them. They want a formula that i can plug into another sheet and have it calculate automotacilly. They have forgotten to 'refresh' pivot tables in the past and have reported the wrong information.

 

[RoryA]

I think you'd be better off automating the refresh of a pivot table. It will be a lot simpler than any formula that can do that, in my opinion.

 

[learningexcel2020]

How do you automate refreshing of a pivot table?

I was thinking if i could create the formula, copy it for every week I need it, it would just populate and management is happy. but i have spent hours trying to get the formula to work. One issue is the employees having multiple labor numbers.

 

[RoryA]

All you need is basically:

Sub UpdatePivotTable()
Sheets("sheet name").Pivottables(1).RefreshTable
End Sub

and then call it from the relevant Worksheet_Activate event so that as soon as you switch to the sheet with the pivot table, it updates.

 

[learningexcel2020]

Is that visual basic?

 

[RoryA]

VBA in fact, but similar enough.

 

[learningexcel2020]

Thanks, i would have to go into the visual basic for excel to write this? Sorry, i haven't done that.

 

[RoryA]

Yes, you would open the VB Editor (Alt+f11), then Insert - Module from the menu, and paste that code in. Then go back to the Excel window, right click the tab for the sheet that has the pivot table on it, choose View Code and then paste this in:

Private Sub Worksheet_Activate()
UpdatePivotTable
End Sub