Thread: GETPIVOTDATA formula help

I am working on a report that is using GETPIVOTDATA formulas to pull in data from a pivot table which is on a separate workbook. Here is the formula:

=GETPIVOTDATA("Tot Pub Est Hrs",'[PRISM Project Estimates_03.09.10.xls]EIT MobSys'!$A$5,"PID","184178","APP","Granite")

The problem is that I would like to copy this formula down the sheet and use information in other columns within this sheet to reference instead of stating the information. For example, instead of using ,"PID","184178" in the above example, I would use ,"PID",B3 & "". This works great for PID but when I apply the same replacement for APP (instead of "Granite") it returns an error.

The APP information is in column U on my spreadsheet. I have tried replacing "Granite" with U3 and U3 & "" and neither work.

Thanks for any help you can provide!

Are you sure that U3 doesn't contain any extra spaces, etc... and is spelled exactly the same...

What does

=Code(Left(U3))

and

=Code(Right(U3))

yield?

you have a special not printable character at the end of the text in U3.. that is code 0160

You can remove it by selecting the cell or cells, then go to Edit|Replace

Hold down the ALT key and enter 0160 (using only the numberpad of the keyboard) in the Find What Field... leave the Replace With field empty and click Replace All.

Or, if you are unable / unwilling to amend the source values you could try embedding your reference to U3 within a SUBSTITUTE call (within the GETPIVOTDATA function)

=GETPIVOTDATA("Tot Pub Est Hrs",'[PRISM Project Estimates_03.09.10.xls]EIT MobSys'!$A$5,"PID",B3&"","APP",SUBSTITUTE(U3,CHAR(160),""))

I was actually going to show that too... but figured it is better to clean your data than to "ignore" it....

Agreed in principle but I think it all boils down to the context (he says, stating the obvious!)

If the corrections should prove to be a one-off exercise then I'm with you...

(IME CHAR(160) often results from web imports... if it transpires that the underlying values are "refreshed" on demand then manual corrections might not be viable and SUBSTITUTE might be a suitable alternative)