Vlookup in a pivot table

[Guest]

I am not sure if this can be done and i have spent days trying to get this to
work (please don't tell my boss), but i have invested so much time, i can't
turn back now...any help would be extremely appreciated

I have a pivot table (example below) which sums the number of people (over
6,000 people in the actual table) that work in a building (100+building in
the actual table), by operating unit. I have a list which contains the
corresponding "building codes." I am trying to write a vlookup formula that
will look to the building code in the list, then return the # of employees in
that building, in a particular operating unit, from the pivot table. The data
in the pivot table will be changing weekly.

So, in Column "B" below, the formula would look to the pivot, see building
code 11 and return the number 16.

List in Sheet1
A B C
Building Code #of Accounting employees #of IT employees
11

___________________________________________________

PIVOT TABLE:
A B C
3Q06
BUILDING CODE OPERATING UNIT Total people
11 Accounting 16
IT 4
11 grand Total 20
___________________________________________________

 

[Guest]

vlookup in pivot table does work but again, you cannot copy the formula in
one cell to another cells easily. A better approach would be to use the
original database with criteria based functions like "if", "sum if", etc...

 

[Lori]

I would have thought that you can get to this result by making a copy
of the pivot table on another sheet then:

1. Double-click the building code heading and select no subtotals
2. Drag the operating unit heading right and up a cell to the column
heading position.
3. Uncheck any other items on the dropdown of the operating unit you
don't want to show (Blanks, etc.)

Alternatively, if your list is fixed and difficult to change you should
be able to use the following formula on sheet1:

=SUMPRODUCT(--(LOOKUP(ROW(Code),ROW(Code)/(Code<>""),Code)=$A2),--(Unit=B$1),Total)

Where the names Code, Unit and Total refer to the A,B,C columns of the
pivot table.
Either replace these by the actual ranges or select A3:A6000 and type
"Code" in the name box next to the formula bar and do the same for Unit
and Total.

 

[Guest]

I am having the craziest time getting a cell reference to calculate: For
example, i have this formula, which works perfect returning the correct
answer of 16:

=SUMPRODUCT(--('3Q06 Occupancy.xls'!Buildingcode_Column="11"),--('[3Q06
Occupancy.xls]Occupancy'!$G$4:$G$159=P1)*('3Q06 Occupancy.xls'!HC_Column)).

However, i want to copy it downward to calculate for multiple values based
off the different in "Buildingcodes". If i change the "11" to a cell
reference which contains the number 11, the formula returns 0.

This formula returns 0:
=SUMPRODUCT(--('3Q06 Occupancy.xls'!Bcode_Column=G54),--('[3Q06
Occupancy.xls]Occupancy'!$G$4:$G$159=P1)*('3Q06 Occupancy.xls'!HC_Column))

where G54 = 11

Any thoughts before i jump out of my cube window?