Cannot use Pivot Table - I think I need sumifs

learningexcel2020

New Member
Joined
Feb 9, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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.
employees.pngemployees.png
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Out of interest, why won't they let you use pivot tables?
 

learningexcel2020

New Member
Joined
Feb 9, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Feb 9, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
All you need is basically:

Code:
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

New Member
Joined
Feb 9, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Is that visual basic?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
VBA in fact, but similar enough.
 

learningexcel2020

New Member
Joined
Feb 9, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
Thanks, i would have to go into the visual basic for excel to write this? Sorry, i haven't done that.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,415
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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:

VBA Code:
Private Sub Worksheet_Activate()
UpdatePivotTable
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,126,996
Messages
5,622,079
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top