I have a table that I want to create a dynamic pivot chart, but I can't figure out how to apply this logic correctly:
If Proposed Cost > 0 and Actual Cost == blank:
Show Proposed Cost ONLY
Else:
Show Actual Cost ONLY
A simple table I have may look like this:
Name // Proposed Cost // Actual Cost
A // 100 // 125
B // 100 // 100
C // 50 //
D // 75 // 150
E // 100
F // 90
What I'd like my pivot table to do is every time I select a macro'd table, it shows me one of two things: JUST the Actual Cost or JUST the Proposed Cost
So for the ACTUAL COST, I'd expect to see info for persons A, B, D
For the PROPOSED COST, I'd expect to see info for persons C, E, F
Is there a way to make this a macro so that Excel is smart enough to know that when I click on the PivotTable fields to clear them and de-select ONLY those fields marked as blank, it will return the expected results in my Pivot Table?
Thank you!
Bookmarks