Formula Cell Name / Reference Problem

[RecLambyUK]

One of our employees who uses spreadsheets (Excel2003) on a regular basis sent me the following email:

-----------------------------------------------------------

Having trouble adding formulas to the sheets on my reports, instead of the cell numbers its comng up with the following:

=GETPIVOTDATA("ORDER O. ",$A$3,"BU","11","RESPONSIBLE","carrier")+GETPIVOTDATA("ORDER NO. ",$A$3,"BU","11","RESPONSIBLE","Customer")+GETPIVOTDATA("ORDER NO. ",$A$3,"BU","11","RESPONSIBLE","dell")

-----------------------------------------------------------

I gather the problem is the formula is using the column name reference instead of the cell reference. Can anybody tell me how to change this ?

 

[SkipVought]

Hi,

The formula is referring to a Pivottable.

Where is the Pivottable?

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[RecLambyUK]

Its located in the same work book on a seperate sheet.

 

[SkipVought]

Notice the ABSOLUTE REFERENCES in the formula and no Sheet reference
[tt]
=GETPIVOTDATA("ORDER O. ",$A$3,"BU","11","RESPONSIBLE","carrier")+GETPIVOTDATA("ORDER NO. ",$A$3,"BU","11","RESPONSIBLE","Customer")+GETPIVOTDATA("ORDER NO. ",$A$3,"BU","11","RESPONSIBLE","dell")
[/tt]
From this formula...

1) the PT must be on the same sheet as the formula

2) the PT must be anchored to A3

Skip,

[red]Be advised:[/red] Alcohol and Calculus do not mix!
If you drink, don't derive!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ222-2244

 

[xlbo]

There is an option under the pivottable toolbar called "Generate GetPivotData" which automatically inserts the Getpivotdata function whenever a cell within a pivottable is referenced.
It is a simple toggle on / off. When turned off, the formulae will be created with normal cell refs. Unfortunately though, any previously created formulae will have to be manually altered

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question