Change GETPIVOTDATA item selection to text instead of cell?

[fouraces]

I have a GetPivotData formula in cell G5 that refers to "Blue" (in cell A5) as item1, and the output of the formula is 100. When I copy the formula to cell G6, of course since GetPivotData locks all the values, my output is still 100 and no cell references have changed. The only piece of the formula I want to change is to refer to "Red" (in A6) instead of "Blue" (in A5) as my item1. However, when I select the tooltip in the formula field for item1, and I click on "Red" Excel inserts A6 into my formula instead of "Red".

The whole point of GetPivotData formulas are to insulate yourself from data changes, which is why I want to use the text values instead of cell references. Do I literally have to copy/paste the text manually every time I change item1 within the copied formula? Is it not capable of grabbing the text in the cell instead, just as it did the first time I built the formula? Maybe there's a key I can press to change whether it grabs the text vs. the cell reference? Creating a list of lookup references seems like excessive work I'd rather not do.

Hopefully there's an answer to this, or else I would consider it a horrific Microsoft oversight. Thanks for any help!

 

[Jerry Sullivan]

Hi fouraces,

You can use relative references in GetPivotData formulas. As an example, convert this formula where "Blue" is A5....
=GETPIVOTDATA("Cost",$A$4,"Color","Blue")

To...
=GETPIVOTDATA("Cost",$A$4,"Color",A5)
or...
=GETPIVOTDATA("Cost",$A$4,"Color",$A5)

 

[Jerry Sullivan]

Re-reading your OP, I think I misinterpreted what you were asking. It sounds like you want a way to have an end result of having "Blue" and "Red" hard-coded into the formula. In my experience with GetPivotData, I almost always have a cells that contain labels with the PivotItem name next to the cells that contain the GetPivotData formulas.

Unknown
	A	B	C	D	E	F	G
1
2
3
4	Row Labels	Sum of Cost				My Report	Cost by Color
5	Blue	22,183				Red	21,159
6	Red	21,159				Yellow	22,443
7	Yellow	22,443
8	Grand Total	65,785
9
Sheet2

Cell Formulas
Range	Formula
G5	=GETPIVOTDATA("Cost",$A$4,"Color",F5)
G6	=GETPIVOTDATA("Cost",$A$4,"Color",F6)

The approach achieves the objective that you noted of insulating yourself from data changes.

Without those labels the resulting values from the formula would have little meaning to person reading the data.

If you had to achieve the end result of having the text of those PivotItems hard coded in the formula, possible workarounds would be:
Use a VBA macro to generate those (for a long list)
Create each hard coded reference by making the entire formula using the "=" then select Pivot Data item method.

 

[fouraces]

Hi Jerry,
Thanks for your reply, yes your second reply gauged it correctly. I was worried that was the answer. In a lot of cases I'm just looking for a quick output, so the hassle of creating a nearby lookup reference table is undesired, but it seems to be the only way. I still find it crazy that in a formula that references locked text values, if I click the item1 tooltip and the cell it gives me the cell reference instead of the text. There should be a toggle key like F4 does for locking references that toggles between the cell reference and it's content. This omission really devalues the formula's efficiency. Oh well, Microsoft is Microsoft. Thank you for your help!