Extracting data from a pivot table in another tab

[ashleynh2011]

Hi there, I've been searching through posts, but I haven't been able to find out whether you can use the GETPIVOTDATA function in one tab while the pivot table is in another tab. Here's what I'm trying to do:

• I have a list of IDs in Tab1 and in Tab2 I have a pivot table counting the number of times those IDs appear in a data data.
• the IDs in the pivot table are in rows and the values are simply a count of those IDs
• In Tab1, I have a table with a number of data points in columns for each of those employee IDs in rows
• I want one of those columns to be the count by ID from the pivot table in Tab2, but based on the ID in column 1 in Tab1

I don't know if you can you vlookup, but that's essentially what I want to do - vlookup based on the ID# in Tab1 and return the count from the pivot table in Tab2. Can this be done because I can't seem to get it to work??

Here's the formula in Tab1 that's returning "#REF!" : GETPIVOTDATA("Employee ID",'Tab2'!$A$7,"Employee ID",'Tab1'!B3)

 

[mrshl9898]

A couple of pictures or a sample would help here. It sounds like you may want to look into COUNTIF

 

[ashleynh2011]

Sorry, it's difficult to show screenshots with confidential data. The screenshot are dummy data. Does this help?

This is sheet1:

This is sheet2:

 

[mrshl9898]

Dummy data is always fine. Looks like =VLOOKUP(B3,'Sheet2'!A:B,2,0) assuming you want a 1 where the error is.

 

[ashleynh2011]

Sorry, sheet 2 is a pivot table...that won't work will it?

 

[mrshl9898]

Doesn't matter if it's a Pivot

 

[ashleynh2011]

I'm getting #N/A though?

 

[mrshl9898]

So i'm guessing

=B3='Sheet2'!A8 is returning FALSE?

Try =NUMBERVALUE() on both cells.What is the result?

 

[ashleynh2011]

so they aren't the same # because of how each list is sorted, but they both return a whole number.

 

[mrshl9898]

Nothing to do with sorting, what did NUMBERVALUE return? I'm assuming one is a Value and one a String. That or there could be other issues.

Chances are one of these should work.

=VLOOKUP(NUMBERVALUE(B3),'Sheet2'!A:B,2,0)
=VLOOKUP(TEXT(B3,"####"),'Sheet2'!A:B,2,0)