Dealing with moving cells in a pivot table

[daveinermo]

Hi All

I have 2 pivot tables which consist of one or two lines with a grand total. One pivot table reflects one hotel chain, one pivot table reflects another.

If the customer has only domestic hotel nights then there is only one line of data per pivot table but if there is international we get two lines in each pivot table plus a total line.

We then have a "sanity check" which does adds the totals for these two pivot tables, minuses this from total paid in the source transactions and this should equal zero, indicating all transactions have been added.


If it doesn't it means there will be some lines of data which have not been allocated to domestic or international.

My problem is the total line can either be U23 or U22 depending on whether there is international travel or not.

When the pivot tables changes the move of the total line from U22 to U23 is not updating the formula so its not working.

Is there a way to get the simple addition formula to move cells if the pivot table expands or contracts once refreshed?

[avk]

Best way to attach your sample file with required result before & after.

[daveinermo]

I just realised I was being a simpleton and could just use getpivotdata!

Sorry!