Dynamic Pivot Table Calculated Field- Question

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
My calculation sheet has merged columns with formulas ranging from Jan-June (and onwards) to calculate total dollars worked by multiplying employee rate * hours worked to get total dollars paid. (see below)

This is a dynamic range so with each passing month, the columns expand to the right.

1627952840114.png


On my other sheet, I have a pivot table with a calculated field that is pulling employee dollars from my calculation sheet.

My pivot table has a calculated field at the end that is summing up total dollars for all months YTD (see below)

1627952858488.png


Now, I also have a macro that can identify which months had charging/did not have charging.
Since there is no charging in May, my macro automatically identifies that there is no data in the column and removes the merged column data from my calculation sheet completely wiping out May data.

When May is completely deleted on my calculation sheet, A '#NAME?' error generates on my calculated field on the pivot table (see below)

1627952991117.png


Is there a way for me to keep my pivot table dynamic, through VBA or some formula manipulation, so that my calculated field is always pulling the data identified in the calculated field formula and not generate an error?


Any help would be greatly appreciated :)

Thank you,
Shane
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,751
Office Version
  1. 365
Platform
  1. Windows
Any reason you don't add a Sumifs formula to the end of your data to calculate the total for each row ?
Calculated field functions are somewhat limited.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,365
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I think your life would be a lot easier if you rearranged the source table into 5 columns: Name, Month, Rate, Hours, Dollars. You could do that with Power Query if you need to keep the original arrangement, but it would make the pivot table a lot simpler.
 

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
I think your life would be a lot easier if you rearranged the source table into 5 columns: Name, Month, Rate, Hours, Dollars. You could do that with Power Query if you need to keep the original arrangement, but it would make the pivot table a lot simpler.
This is a simplified version of my actual data set. The actual data set goes across thousands of columns for each month.

I would like to keep the format and adjust the calculated field formula if possible.
 

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Any reason you don't add a Sumifs formula to the end of your data to calculate the total for each row ?
Calculated field functions are somewhat limited.
I do not want to add additional columns unrelated to the monthly data on my calculation sheet since the data is constantly expanding to the right.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,365
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The actual data set goes across thousands of columns for each month
You could still use PQ as an intermediate step just to reformat the data source for the pivot table. It would make life much simpler, and allow you to keep the original format of the worksheet data.

This video should give you an idea of what I mean:
 
Last edited:
Solution

ShaneB614

New Member
Joined
Jun 17, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

You could still use PQ as an intermediate step just to reformat the data source for the pivot table. It would make life much simpler, and allow you to keep the original format of the worksheet data.

This video should give you an idea of what I mean:

You could still use PQ as an intermediate step just to reformat the data source for the pivot table. It would make life much simpler, and allow you to keep the original format of the worksheet data.

This video should give you an idea of what I mean:
Thank you for the info. I'll take a look at the video and incorporate it into my data set.

Would PQ interfere with any VBA code that I have in my file?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,365
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It depends what exactly your code does, but generally no.
 

Forum statistics

Threads
1,141,715
Messages
5,708,052
Members
421,541
Latest member
Akidev

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