May 17 2019 12:49 AM
Is it possible to create a "Excel formula" where it returns the value of the cell (or) row number and column number when it matches with the specific row name and column name.
For example:I need value 918 or row number and column number of the cell where the row name is "AMS" and column name is "MRO4".
I can't able to fetch the value using row and column number because each time new column and row is added and table structure is changing so does the row and column number values.
May 17 2019 01:05 AM
Solution@suthandira , for such sample
it'll be
=GETPIVOTDATA("[Measures].[Sum of C]",$F$5,"[Table1].[A]","[Table1].[A].&[MRO2]","[Table1].[B]","[Table1].[B].&[AMS]")
The easiest way to generate the formula - type "=" in any empty cell and click on proper cell within your PivotTable. After that you may play with adjustments.
May 17 2019 03:22 AM
May 17 2019 04:18 AM
@suthandira , yes, for the table2 like this
that is
=INDEX(Table2[#All], MATCH("MRO2",Table2[[#All],[Row Labels]],0), MATCH("AMS",Table2[#Headers],0))
May 17 2019 04:58 AM
May 17 2019 05:08 AM
@suthandira you are welcome
May 17 2019 01:05 AM
Solution@suthandira , for such sample
it'll be
=GETPIVOTDATA("[Measures].[Sum of C]",$F$5,"[Table1].[A]","[Table1].[A].&[MRO2]","[Table1].[B]","[Table1].[B].&[AMS]")
The easiest way to generate the formula - type "=" in any empty cell and click on proper cell within your PivotTable. After that you may play with adjustments.