Pivot table headers

sdurocher

Board Regular
Joined
Jul 28, 2006
Messages
93
I have a pivot table that has earning amounts as the data, and the coulmn is the department code, also by check date (one date selected at a time). This pivot table is reading data by employee, by check date and summing it. I am copying and pasting these summed amounts into my journal entry spreadsheet, that breaks out the data further via formulas.

The problem I am encountering is that not all earning codes are used on every check date. When there is no data for a earnings amount, it does not show up on the pivot table. So when I copy and paste, I have to do some adjustments to fit my template that feeds formulas. It's not too big of a deal, but I want to automate the procedure.

Is there a way to have a pivot table show header detail for a earnings amount (data) or a depatment code (column) even if there is no data on that particular check data? The date would be present in other parts of the year, just not that particular check date.

My goal is to either have my formulas read my pivot table (in a round about way), with out me having to do anything manual (besides changing the check date)

Thank you for any help you can provide
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Have you considered using the GETPIVOTDATA function rather than Copy/Paste? For items that don't exist, it will return #N/A, which you can trap with IF.
 

sdurocher

Board Regular
Joined
Jul 28, 2006
Messages
93
When I put in the getdata, it returns a #REF! when I choose a date that does not have that data.

Would I just change my formulas that links to that cell to a if statement that either takes a value or has #REF! equal zero?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You could use eg:

=IF(ISERROR(GETPIVOTDATA(YourFormula)),"",GETPIVOTDATA(YourFormula))
 

sdurocher

Board Regular
Joined
Jul 28, 2006
Messages
93
Works perfect, that formula will save me tons of time and I will have many uses for it.

Thank you
 

Forum statistics

Threads
1,136,990
Messages
5,678,979
Members
419,796
Latest member
doctorgresham

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