I have a pivot table that is pulling inventory values for various 'kits' and summarizing by customer, then using formulas adjacent to the pivot table to pull in revenues for that customer (on the total line only) in order to calculate ROI, etc. I can't pull the revenue into the table because it displays the entire revenue line on every inventory kit line, then the summary overstates the customer revenue by adding all of the lines up (hope that makes sense).
My challenge is that I have multiple regions also in the pivot table (sorts/summarizes by region, then customer), so if I change the pivot table select field to only display one of the regions (for printing and emailing), the formulas out to the right of the pivot table are fine - they're just all still there as though the entire pivot table report were being viewed.
I'm hoping there's a way to set the pivot table to copy formulas up/down such that they match the number of rows in the pivot table (like you can do with queries), but I can't find a way to do that - any suggestions?
Thanks!
Bookmarks