lookup from a table in a pivottable

[Peter]

Hi
(excel 2007)

I have a regular table (Table13) with some productinformations. I
column M i have the SerialId for the specific item.
I need to check if the SerialId already is in our ERP-system (ax). I
have a pivottable showing all SerialId's from my Ax-table. Excel has
given the pivttable the name Pivottable1.

I now want to write a formular like

=VLOOKUP(Table13[[#This row];[SerialID]];pivottabel1;1;True)

But it seems not to be possible to lookup into a pivottable ???

Regards

Peter

 

[Roger Govier]

Hi Peter

You could just use a MATCH of your ID against the column of the PT sheet
containing the Serial ID's

=MATCH(Table13[[#This row];[SerialID]];Sheet4!D;True)

 

[Peter]

Hi Roger.

Thank you. It was helpfull. My own solution was much more complicated.

I do, however, wonder if it is possible to refer to a pivottable from
a lookup ?

Regards

Peter

 

[Roger Govier]

Hi Peter

I am certainly not aware of any method.
The only inbuilt function that will allow extraction of data from a PT
is the GetPivotData function.
I suppose you could construct a formula using GetPivotData, substituting
your table value as one of the parameters, and testing whether you got
an error as response.

It would be very ungainly and quite inefficient compared with the simple
Match.

The only other way would be to write your own function in VBA.

 

[Tom]

Hi Peter

I am certainly not aware of any method.
The only inbuilt function that will allow extraction of data from a PT is
the GetPivotData function.
I suppose you could construct a formula using GetPivotData, substituting
your table value as one of the parameters, and testing whether you got an
error as response.

It would be very ungainly and quite inefficient compared with the simple
Match.

The only other way would be to write your own function in VBA.

At last, I get to answer a question no-one else can.

Here's how I do it.

Run your pivot table, arrange it how you want it ,

select the rows and columns that contain your data and right click, and
"name a range"

run your pivot table again and arrange the data the same as you did last
time

Use that named range in your lookups

Before you change anything else on your pivot table, copy the row your
lookup is in and paste special values, as otherwise it will change when your
pivot table changes

I use this method on a monthly basis.

Tom