Forum Discussion
PatDools
Apr 16, 2023Brass Contributor
Excel function to capture columns of pivot table values?
Hello, I am aware of the GETPIVOTDATA function in Excel, but, as far as I can tell, it can only grab a value from a particular cell in a given Pivot Table. Is there a way to copy a whole area of Pi...
- Apr 17, 2023
You can use the INDEX and MATCH functions together to capture columns of Pivot Table values in Excel.
Here’s how:
- Select the cell where you want to display the first value from your Pivot Table.
- Type =INDEX( in the formula bar and select the range of cells that contains your Pivot Table data.
- Type ,MATCH( and select the cell that contains the column heading for the data you want to capture.
- Type ,0) to complete the formula.
This will return the first value from your Pivot Table column.
To capture multiple values, you can copy this formula down to other cells in the same column.
Here’s an example:
Let’s say you have a Pivot Table with the following data:
Region
Sales
East
$100
West
$200
North
$300
South
$400
To capture the values in the “Sales” column, you would use the following formula in cell B2:
=INDEX($A$2:$B$5,MATCH($A2,$A$2:$A$5,0),2)
This formula will return the value in the “Sales” column for the region listed in cell A2.
To capture the values for other regions, you can copy this formula down to other cells in column B.
I hope this helps!
Eugene Cloud
Jun 12, 2024Copper Contributor
Perhaps this might be a solution.
If someone will tell me how to link to my workbook I will post it.
I use measures.
Row LabelsSumCtIndXtCf>Indx<Grand Total1659816.800
A | 1890 | 1.44 | 0 | 0 |
C | 1176 | 1.28 | 0 | 0 |
E | 1787 | 1.44 | 0 | 0 |
G | 1282 | 0.64 | 1282 | 0.64 |
I | 1183 | 1.28 | 0 | 0 |
K | 952 | 1.28 | 0 | 0 |
M | 1767 | 2.4 | 0 | 0 |
O | 886 | 1.28 | 0 | 0 |
Q | 1489 | 1.76 | 0 | 0 |
S | 1341 | 0.96 | 1341 | 0.96 |
U | 1070 | 0.96 | 1070 | 0.96 |
W | 890 | 1.28 | 0 | 0 |
Y | 885 | 0.8 | 0 | 0 |