Pivot table headers

[sdurocher]

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

 

[Andrew Poulsom]

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]

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]

You could use eg:

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

 

[sdurocher]

Works perfect, that formula will save me tons of time and I will have many uses for it.

Thank you