getting data from a pivot table

[Kevin]

I'm setting up a sheet to track expenditures. I've got a list (to copy
formulas and add rows). I then created a dynamic range (from
http://www.ozgrid.com/Excel/DynamicRanges.htm). I use this range to create
my pivot. That gives me a sum of G/L account per month, and of course the
YTD sum per account. So far so good.

I want to take each of those YTD values on another sheet to create a budget
variance report. How do I reference the calculated totals from the pivot?
I could use a cell reference, but that will change as data is added to the
list and the pivot refreshed.

Thanks for any pointers.
Kevin

--

 

[Debra Dalgleish]

You can use the GETPIVOTDATA function to extract data. Look in Excel's
help for information and examples for your version of Excel.

 

[Kevin]

SWEEEEEEEEEEEEEET. Thanks for the fast response. That was the clue I
needed!

 

[Kevin]

Debra, went to your web site and you've got a sheet that is 90% of what I'm
doing. The major difference is that your actual sheet is where I'm doing my
pivot. I'm using the pivot to summarize the details from a data entry list.

Now after looking at yours, I'm thinking I may turn the actuals into a set
of dsum functions rather than a pivot. I think that may be easier to then
incorporate into the ytd and variance sheet. Any thoughts?

Thanks again

 

[Debra Dalgleish]

Without knowing any of the details, it's hard to offer advice, but
instead of DSUM, you may find it easier to use SUMPRODUCT to summarize
the data. It doesn't require a criteria area -- instead, you could refer
to the column and row headings in the summary table.