CODEWITHSUNDEEP
excelmacrosvba
Motombo
Motombo

Reputation: 1787

Dynamically look for corresponding element in Pivot Table

Currently I have a pivot table of the form

Employee No.   Hours
101010         150
12456          140
 etc           etc

Now there is another spreadsheet I have which Currently Looks like this

Employee No. Name  Level Organization Code Manager Organization Code etc
101010       Bob    CP3    etc         etc   etc        etc
12456        Jane   CP2    etc         etc   etc        etc

The second sheet is automatically pulled from a third party software. Unfortunately it does not pull the hours of that employee as well. This report also sometimes jumbles employee numbers in different orders every month so I need something dyanmic while will match the employee No, with the employee No in the pivot table.

For example suppose we have

Employee No. Name  Level Organization Code Manager Organization Code etc
101010       Bob    CP3    etc         etc   etc        etc

The macro should then look for an Employee No of the given row we are at in the pivot table. Basically it will save 101010 in memory, and loop through the pivot table. Find the matching Employee No and add another column labeled hours with the respective hour for that row.

The desired spreadsheet will look like

Employee No. Name  Level  Hours  Organization Code Manager Organization Code etc
101010       Bob    CP3    150         etc         etc   etc        etc
12456        Jane   CP2    140        etc         etc   etc        etc

How would I go about achieving this?

Upvotes: 0

Views: 105

Answers (1)

Mats Lind
Mats Lind

Reputation: 934

With "Eno" in stead of "Employee No." assuming that we are on row 2 the pivot table resides starts at AA1 and Eno in the A column, in the Hours column insert the formula =GETPIVOTDATA("Hours",$AA$1;"Eno",A2)

Formula row shows J6

Upvotes: 1

Related Questions