Identify column in pivot table by data name

J

Jenn

Is there anyway to identify a column in a pivot table by the name of the
data. Meaning can I say "Find the coumn in this pivot with this header name
and return its value to me (i.e., tell me what column it is: A, B, C, D,
etc)".
Thanks in advance!
 
D

Debra Dalgleish

You can use the GETPIVOTDATA function to extract the data. The
function's arguments vary, depending on the version of Excel you're using.

For Excel 2002, to return the total for "Pencils":

=GETPIVOTDATA("Total",$A$4,"Item","Pencils")

Look in Excel's online help for examples for your version, and there are
a couple of examples here:

http://www.contextures.com/xlPivot06.html#Formula
 
J

Jenn

This will help but I also really need (using your example) that total is in
column B. I don't care about the numbervalues in the column, only what column
we'll say "total" is in. Is there a way to do this?
Thanks again.
 
M

Max

Jenn said:
This will help but I also really need (using your example) that total is in
column B. I don't care about the numbervalues in the column, only what column
we'll say "total" is in. Is there a way to do this?
Thanks again.
Click to expand...

Maybe try something along these lines ..

Assuming the pivot is in Sheet1, col labels in row2,
with "Total" in B2

In Sheet2
------------
Put in A1: Total
Put in B1: =CHOOSE(MATCH(TRIM($A1),Sheet1!$2:$2,0),"A","B","C","D","E")
B1 will return "B"

The example formula in B1 stops at col "E".
Just add more to the formula as needed ("F","G",...)
but only up to the max 29 args for CHOOSE, i.e. up to col "AC" only
 
M

Max

Some clarifications ..
Assuming the pivot is in Sheet1, col labels in row2,
with "Total" in B2
Click to expand...

The "col labels in row2" should read as:
"col labels in row2 (in A2 across)"

And with error-trapping to avoid #NAs in case there's
no match for the column header entered in A1,
the formula in B1 would be :

=IF(ISNA(MATCH(TRIM($A1),Sheet1!$2:$2,0)),"",CHOOSE(MATCH(TRIM($A1),Sheet1!$
2:$2,0),"A","B","C","D","E"))

which would return blanks ("") for unmatched cases
 
Top