pivot table pull value if name changes

[jozeklon]

Hi

I have a pivot table with several UNITS (unit 1, unit 2, unit 3… etc) in rows and then in the columns I have different cost points for each unit. Last row is a calculated field SUM OF ALL COSTS.

In second sheet of the same document, I pull the unit name and total cost of each unit to create a quote document for the client. This all works great, but if you change the name of the unit, the pulled value return error NA due to different name. I know this is expected behaviour for this, but is there a workaround when name changes?

I can not keep the same name always as from project-to-project unit names change.

Thank you

 

[Kaper]

I'm not sure which method you use to "pull" data from pivot. As you are using Excel 2021, you can make a list of all units using UNIQUE function UNIQUE function - Microsoft Support and then pull costs of unit from pivot