Hi folks,
I run a report that shows sales by locations on one sheet and consignment sales on another. Both are pivot tables. I want users to see their total sales. I've linked the grand totals row from each pivot table to the thirds sheet. BUT the pivot table will grow or decrease in rows depending on what's sold so my link will not consistently give me the grand totals from each sheet.
How can I link the grand totals for each pivot table to another sheet?
Thanks,
Pat
Date
Votes
3 comments
-
Jet Reports Historic Posts Official comment This is actually an Excel trick - not a Jet Reports one…
Try referencing the cell in question using the "GETPIVOTDATA" formula - this formula works very similarly to Jet functions in that you have to reference the position on the Pivot table that you want (based on column headers and row titles).
For instance, if I have a table that lists values for each sales person (row data), and by year (column data) - the GETPIVOTDATA formula =GETPIVOTDATA("Sales Rep","ADAM ADAMS","YEAR",2013) - will give me the total for Adam for 2013 - but I could just as easily replace "ADAM ADAMS" with a cell reference for changing data.
the same holds true for the total row - instead of referencing "Sales Rep","ADAM ADAMS" - by leaving that portion of the 'filter' blank - I get the total for the entire group.
Here's a link with more info: http://www.databison.com/getpivotdata-formula-excel-how-to-use-getpivotdata-function-with-pivot-table/ -
Jet Reports Historic Posts Heather,
The GETPIVOTDATA formula is the answer! I used that as was able to filter and update my pivot tables AND my totals were also updated even when the size of the pivotal table changed.
Thanks!
Pat -
Jet Reports Historic Posts Glad I was able to help!
When I first started working with the GETPIVOTDATA formula, it was a bit confusing - but it's saved me hours of fussing now that I've found it.
And of course, it's nice how the basic structure is similar to referencing filters in Jet.
Please sign in to leave a comment.