Dynamic Pivot Table Calculated Field- Question

[ShaneB614]

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

 

[Alex Blakenburg]

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]

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]

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]

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]

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:

 

[ShaneB614]

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]

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

 

[ShaneB614]

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

Awesome. Thanks for your help, Rory!