Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup in a pivot table
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 __________________________________________________ _ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup in a pivot table
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... "Spidey" wrote: 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 __________________________________________________ _ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup in a pivot table
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. Spidey wrote: 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 __________________________________________________ _ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup in a pivot table
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? "Lori" wrote: 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. Spidey wrote: 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 __________________________________________________ _ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom field in Pivot Table? | Excel Discussion (Misc queries) | |||
Problem with VLOOKUP and pivot table | Excel Discussion (Misc queries) | |||
Top 5 plus Other in Pivot table | Excel Worksheet Functions | |||
pivot table created from another pivot table | Excel Worksheet Functions | |||
Pivot -- want to use Max and Sum in same table | Excel Worksheet Functions |