Extracting data from a pivot table in another tab

ashleynh2011

New Member
Joined
Jun 12, 2020
Messages
13
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)
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,633
A couple of pictures or a sample would help here. It sounds like you may want to look into COUNTIF
 

ashleynh2011

New Member
Joined
Jun 12, 2020
Messages
13
Sorry, it's difficult to show screenshots with confidential data. The screenshot are dummy data. Does this help?

This is sheet1:
1596671113444.png


This is sheet2:
1596670557729.png
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,633
Dummy data is always fine. Looks like =VLOOKUP(B3,'Sheet2'!A:B,2,0) assuming you want a 1 where the error is.
 

ashleynh2011

New Member
Joined
Jun 12, 2020
Messages
13

ADVERTISEMENT

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

ashleynh2011

New Member
Joined
Jun 12, 2020
Messages
13

ADVERTISEMENT

I'm getting #N/A though?
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,633
So i'm guessing

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

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

ashleynh2011

New Member
Joined
Jun 12, 2020
Messages
13
so they aren't the same # because of how each list is sorted, but they both return a whole number.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,633
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,071
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top