| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Fomula in a Pivot Table
Is it possible to use the VLOOKUP formula in a pivot table?
I currently have a pivot table set up and would like to use the Vlookup function to be able to bring in data stored on a different sheet to the original data, but when I enter the formula it is indicating that there is an error! I have entered the formula as follows: =vlookup('MAT SPEC',sheet1!A:B,2,0) When the error message comes up and I click ok it highlights the table array as the problem area. And anyone help. Thanks |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a Fomula in a Pivot Table
Add a column to your source data range and do the Vlookup in there.
-- HTH... Jim Thomlinson "TheRook" wrote: Is it possible to use the VLOOKUP formula in a pivot table? I currently have a pivot table set up and would like to use the Vlookup function to be able to bring in data stored on a different sheet to the original data, but when I enter the formula it is indicating that there is an error! I have entered the formula as follows: =vlookup('MAT SPEC',sheet1!A:B,2,0) When the error message comes up and I click ok it highlights the table array as the problem area. And anyone help. Thanks |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a Fomula in a Pivot Table
Tried that but will not work because the information I am using the pivot for
is to give me a sum of raw material required by part, then the lookup would give me the current stock. If I put the lookup at source it would multiple the current stock by the number of times it arrears. If that makes sense? "Jim Thomlinson" wrote: Add a column to your source data range and do the Vlookup in there. -- HTH... Jim Thomlinson "TheRook" wrote: Is it possible to use the VLOOKUP formula in a pivot table? I currently have a pivot table set up and would like to use the Vlookup function to be able to bring in data stored on a different sheet to the original data, but when I enter the formula it is indicating that there is an error! I have entered the formula as follows: =vlookup('MAT SPEC',sheet1!A:B,2,0) When the error message comes up and I click ok it highlights the table array as the problem area. And anyone help. Thanks |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a Fomula in a Pivot Table
do it adjacent to the table. You can not edit the cells in a pivot table.
-- Regards, Tom Ogilvy "TheRook" wrote: Tried that but will not work because the information I am using the pivot for is to give me a sum of raw material required by part, then the lookup would give me the current stock. If I put the lookup at source it would multiple the current stock by the number of times it arrears. If that makes sense? "Jim Thomlinson" wrote: Add a column to your source data range and do the Vlookup in there. -- HTH... Jim Thomlinson "TheRook" wrote: Is it possible to use the VLOOKUP formula in a pivot table? I currently have a pivot table set up and would like to use the Vlookup function to be able to bring in data stored on a different sheet to the original data, but when I enter the formula it is indicating that there is an error! I have entered the formula as follows: =vlookup('MAT SPEC',sheet1!A:B,2,0) When the error message comes up and I click ok it highlights the table array as the problem area. And anyone help. Thanks |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating a Fomula in a Pivot Table
Change your aggregation from Sum to Average so that instead of getting the
sum of the same item appearing multiple times you will get the average (which should be the number you are looking for since the average of a varaible number of x's is going to be x)... -- HTH... Jim Thomlinson "TheRook" wrote: Tried that but will not work because the information I am using the pivot for is to give me a sum of raw material required by part, then the lookup would give me the current stock. If I put the lookup at source it would multiple the current stock by the number of times it arrears. If that makes sense? "Jim Thomlinson" wrote: Add a column to your source data range and do the Vlookup in there. -- HTH... Jim Thomlinson "TheRook" wrote: Is it possible to use the VLOOKUP formula in a pivot table? I currently have a pivot table set up and would like to use the Vlookup function to be able to bring in data stored on a different sheet to the original data, but when I enter the formula it is indicating that there is an error! I have entered the formula as follows: =vlookup('MAT SPEC',sheet1!A:B,2,0) When the error message comes up and I click ok it highlights the table array as the problem area. And anyone help. Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I detail Pivot Table data without creating a table (Excel2007) | Excel Discussion (Misc queries) | |||
| How can I detail Pivot Table data without creating a table (Excel2007) | Excel Discussion (Misc queries) | |||
| creating a report from a table, pivot table not suitable | Excel Worksheet Functions | |||
| creating a pivot table from 4 pivot tables | Excel Worksheet Functions | |||
| Creating Pivot Table with VBA | Excel Programming | |||
Linear Mode
