Vlookup/Get Pivot/Index Match without converting reference cell to text

[tinydancer]

I am trying to find a method for pulling a value from a pivot table using a reference cell. My first thought was to use GETPIVOTDATA, but when I use an "=" and then click on the cell I want it comes back:

=GETPIVOTDATA("Actual FTE's",$A$17,"SSC|DEPT Concat","8591945","SSCCOID",8591,"Month",5).

That number "8591945" is what I need to replace with a reference cell of lets say B2. But when I plug it in there, I get a #REF!. I can use a reference cell for the 8591 part, but it won't let me for 8591945.

I've tried a Vlookup and Index Match as well, but with those I have to convert my reference cell to text and that messes up the rest of a very large workbook. Please advise on possible solutions, hope this is a quick fix and I'm just missing something.

Thank you

 

[Radoslaw Poprawski]

Try this:

1. Select a cell inside the pivot table.
2. Go to the Options/Analyze tab in the Ribbon.
3. Click the small drop-down arrow next to Options
4. You should see a check mark next to the option, Generate GETPIVOTDATA. This means the feature is currently On. Click the button to toggle GETPIVOTDATA Off.

Also go to excel options:
Data: Edit Default layout.
on the bottom select Tabluar form.

 

[tinydancer]

I did the first part, where is Edit Default Layout under Excel Options?

 

[Radoslaw Poprawski]

 

[tinydancer]

For whatever reason I do not have that option, closest thing I have is "Data" in the Advanced tab. Still not having any luck, is there another solution?

 

[Radoslaw Poprawski]

That is just how the table would be visable.
Tabulated form is the most preferable one.
it seems like I'll have to set it up manualy.

 

[tinydancer]

I've figured out the solution, problem was it referring to a value field cell. Had to change up how you insert that cell into the formula:

Original : =GETPIVOTDATA("Actual FTE's",$A$17,"SSC|DEPT Concat","8591945","SSCCOID",8591,"Month",5)
New: =GETPIVOTDATA("Actual FTE's",$A$17,"SSC|DEPT Concat",B2 & "","SSCCOID",8591,"Month",5)

Thank you for the help on this.