GETPIVOTDATA() Help

[BrianM]

I'm not sure how to use the GETPIVOTDATA() function or if it will do what I'm looking for. I just want to subtract the amount in the pivottable by 6. Or should I just use =C4-6

Thanks,
Brian

Worksheet in Basis (1)
	B	C	D	E
2	Count of Material
3	Material	Total
4	115444	35	29
5	115515	28	29
6	116860	20	29
7	Grand Total	83	29
Sheet2

 

[KenWright]

If you don't want it don't use it - view toolbars / pivot table toolbar - use the GetPivotData icon to turn it off.

 

[BrianM]

It's not that I don't want to use it, but want to understand it. When I copy it down it appears to be absolute; nothing changes. Can GETPIVOTDATA() do this, or is it not intended for this type of equation?

Thanks,
Brian

 

[folderol]

You need to remove most if not all of the constants in a getpivotdata statement, if you want to copy/paste the formula to other cells.

Typically, I would change yours to read

=GETPIVOTDATA("Material",$B$2,$B$3,B4) - 6

The first parameter is the fieldname of the total column whose value you are returning, the 2nd is a constant that identifies the pivottable to use, 3rd and 4th are the attributes you are filtering on.

You can use INDIRECT($F$1) in place of $B$2 to have an additional criteria to choose from among several pivottables, instead of hardcoding to just one. The cell F1 must evaluate to an address that falls within a pivottable or an error results.

If you substitute a formula for the first parameter, the formula must evaluate to the name of a column in the data area (check the Wizard,Layout to see what columns can be used).

 

[folderol]

By the way, I forgot to mention that getpivotdata() works exceptionally well in Excel 2003. I didn't use it much in 2002, and could not get it to do anything useful in 2000.