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