GetPivotData Question

[kellexlsx]

I have a pivot table that provides the data for individual employee reports. The employee's name is in cell A1 of each report, and it shows an amount by line of business. Each employee name is also an item in the pivot table. Lines of business are items in another pivot table field. Is there a way to reference the the cells in the left column of the (very simplified) table below, so that the items in the GETPIVOTDATA formula needn't be manually keyed in for each employee's report?

I should probably note that the employee's name is the result of a formula referencing the sheet name - not a text string.

SMITH	Amount
LOB1	=GETPIVOTDATA("AMOUNT",Sheet1!$A$3,"EMPLOYEE","SMITH","LOB","LOB1")
LOB2	=GETPIVOTDATA("AMOUNT",Sheet1!$A$3,"EMPLOYEE","SMITH","LOB","LOB2")
LOB3	=GETPIVOTDATA("AMOUNT",Sheet1!$A$3,"EMPLOYEE","SMITH","LOB","LOB3")

<tbody>
</tbody>

 

[Jerry Sullivan]

Hi kellexlsx,

Yes, you can replace the keyed in strings with references to those cells.

If SMITH is in Z1 and LOB1 is in Z3, return the value related to LOB1 with:
=GETPIVOTDATA("AMOUNT",Sheet1!$A$3,"EMPLOYEE",Z$1,"LOB",Z3)