Fern said:
=+GETPIVOTDATA(pivot1,$A$3 & " " &B$2 & " " &A5)
I'm trying to return pivot table data to another sheet. The range of the
pivot table is called "pivot1" and the reference cells represent (name of
doc), (date), (procedure type). I get a REF#
I have spent hours already and need a solution fast.
Please
Hi Fern,
Spend a few seconds looking at the syntax for GETPIVOTDATA and you will
probably realize what you are doing wrong.
IME, constructing a GETPIVOTDATA formula is easiest like this. This is a
bit verbose, but it goes quickly and is much easier than writing
GETPIVOTDATA by hand:
1) Cursor in cell where formula will reside.
2) Type = and navigate to a data cell in the PT. Don't worry too much
about the cell you select -- the data element (count of procedures, sum
of billed amount, etc.) should be the one you want, but the category
combinations do not have to be correct at this point. Press Enter. You
now have a formula that returns the correct kind of data, but from
specific (and perhaps incorrect) categories. Now generalize this to suit
your needs.
3) Click the formula in the formula bar. A pop-up should reveal the
syntax. As you hover over the parameters in the pop-up, they will
underline as hyperlinks.
4) One by one, click the parameters in the pop-up. Note how the
corresponding elements in your formula are highlighted.
5) Now you can carefully edit your formula, replacing the hard-coded
categories with desired cell references.
