# Dynamic Pivot Table Calculated Field- Question

#### ShaneB614

##### New Member
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.

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)

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)

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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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 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.

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.

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.

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

It depends what exactly your code does, but generally no.

It depends what exactly your code does, but generally no.
Awesome. Thanks for your help, Rory!

Replies
4
Views
361
Replies
1
Views
630
Replies
13
Views
387
Replies
0
Views
159
Replies
7
Views
125

1,196,507
Messages
6,015,604
Members
441,905
Latest member
Jean207

### 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.

### Which adblocker are you using?

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

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