Hello,
This one might be a bit of a stretch, but I thought I'd ask anyway. I have some columns and rows next to a pivot table that contain a lot of logic to do COUNTIFS on other sheets, based on values from the pivot table. All that is working great. The problem is that, on a row below these columns and rows, I have a SUM() formula to get the total for each column. I would like that SUM() row to move as the pivot table headers are collapsed.
I tried using an IF statement in all the rows and columns to the right of the pivot table that checks that cell's position relative to "Grand Total". So IF below "Grand Total" do the SUM(), but IF above do my other formula. The problem is that the IF FALSE part of the statement does SUM(E$7:E$11), which creates a circular reference. Can anyone think of a way to make that summation row move as the pivot table expands and shrinks that resolves the circular reference?
This one might be a bit of a stretch, but I thought I'd ask anyway. I have some columns and rows next to a pivot table that contain a lot of logic to do COUNTIFS on other sheets, based on values from the pivot table. All that is working great. The problem is that, on a row below these columns and rows, I have a SUM() formula to get the total for each column. I would like that SUM() row to move as the pivot table headers are collapsed.
I tried using an IF statement in all the rows and columns to the right of the pivot table that checks that cell's position relative to "Grand Total". So IF below "Grand Total" do the SUM(), but IF above do my other formula. The problem is that the IF FALSE part of the statement does SUM(E$7:E$11), which creates a circular reference. Can anyone think of a way to make that summation row move as the pivot table expands and shrinks that resolves the circular reference?
