Forum Discussion
Using data validation values as dynamic feeds in a Getpivottable command.
I have a report that I am collecting summarized data from multiple pivot tables into a summary report, please see equation below:
=GETPIVOTDATA("[Measures].[Sum of Posting_Amount]",'month Facility Departmental'!$A$6,"[Wolfs_Expenditures].[Fund]","[Wolfs_Expenditures].[Fund].&[001]","[Wolfs_Expenditures].[Object]","[Wolfs_Expenditures].[Object].&[0201]","[Wolfs_Expenditures].[Object Series]","[Wolfs_Expenditures].[Object Series].&[200]","[Wolfs_Expenditures].[FYFP 1]","[Wolfs_Expenditures].[FYFP 1].&[20257]","[departementaldescr].[Dept Name]","[departementaldescr].[Dept Name].&[Administration]")
I am wanting to use a cell value that can change to equal a value from a pick list selection to change the value in the GETPIVOTDATA equation, [Administration]. If the pick list exists in cell b3, how do I reference that cell in the location of [Administration]?
Thank you,
You can achieve this by modifying the GETPIVOTDATA function to dynamically reference the value in cell B3. The key is to replace the hardcoded [Administration] value with a reference to B3. Here's how you can do it:
Adjust Formula and use:
=GETPIVOTDATA("[Measures].[Sum of Posting_Amount]",'month Facility Departmental'!$A$6,"[Wolfs_Expenditures].[Fund]","[Wolfs_Expenditures].[Fund].&[001]","[Wolfs_Expenditures].[Object]","[Wolfs_Expenditures].[Object].&[0201]","[Wolfs_Expenditures].[Object Series]","[Wolfs_Expenditures].[Object Series].&[200]","[Wolfs_Expenditures].[FYFP 1]","[Wolfs_Expenditures].[FYFP 1].&[20257]","[departementaldescr].[Dept Name]", "[departementaldescr].[Dept Name].&[" & B3 & "]")
Belzic,
Thank you I had been attempting to use the & and was getting errors but I was not able to find anything to show me the syntax. I greatly apprecciate your input as it really builds on my ability to expand my tool box!
Thank you!
