Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup tables
i have 20 separate spreadsheets - one for each cost center - the spreadsheets
show actuals for the months July through nov. i want to add december's actuals. i know that i can download the dec actuals into an excel spreadsheet and do a vlookup to put them in dec. every time that i've done a vlookup i have put the table in the same spreadsheet. since i don't want to copy this table to 20 spreadsheets i want to know if it's possible to have the table in a separate spreadsheet. thanks in advance for your help. your suggestions in the past have been VERY helpful. -- aprilshowers |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup tables
You can use =vlookup() and point at a range in another workbook.
I like to let excel worry about the syntax, so I do this: I open the workbook with the table (I'll use book2.xls in my example) I open the workbook that will contain the formula. I type: =vlookup(a1, in a cell (adjust the A1 to what you want) Then I use Window to select the workbook with the table. I select the worksheet with the table I select the table and hit comma. My formula will look like: =vlookup(a1,[book2.xls]Sheet1!$A:$G, And I finish up the formula: =vlookup(a1,[book2.xls]Sheet1!$A:$G,3,false) (say) When I close that workbook with the table, excel will change the formula: =VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$G,3,FALSE) (If you're good (or just persistent), you can actually type the formula from scratch!) april wrote: i have 20 separate spreadsheets - one for each cost center - the spreadsheets show actuals for the months July through nov. i want to add december's actuals. i know that i can download the dec actuals into an excel spreadsheet and do a vlookup to put them in dec. every time that i've done a vlookup i have put the table in the same spreadsheet. since i don't want to copy this table to 20 spreadsheets i want to know if it's possible to have the table in a separate spreadsheet. thanks in advance for your help. your suggestions in the past have been VERY helpful. -- aprilshowers -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
vlookup tables
thank you so much. it works like a charm - i will adjust the formula to
eliminate the NA errors. thanks for your help -- aprilshowers "Dave Peterson" wrote: You can use =vlookup() and point at a range in another workbook. I like to let excel worry about the syntax, so I do this: I open the workbook with the table (I'll use book2.xls in my example) I open the workbook that will contain the formula. I type: =vlookup(a1, in a cell (adjust the A1 to what you want) Then I use Window to select the workbook with the table. I select the worksheet with the table I select the table and hit comma. My formula will look like: =vlookup(a1,[book2.xls]Sheet1!$A:$G, And I finish up the formula: =vlookup(a1,[book2.xls]Sheet1!$A:$G,3,false) (say) When I close that workbook with the table, excel will change the formula: =VLOOKUP(A1,'C:\My Documents\excel\[book2.xls]Sheet1'!$A:$G,3,FALSE) (If you're good (or just persistent), you can actually type the formula from scratch!) april wrote: i have 20 separate spreadsheets - one for each cost center - the spreadsheets show actuals for the months July through nov. i want to add december's actuals. i know that i can download the dec actuals into an excel spreadsheet and do a vlookup to put them in dec. every time that i've done a vlookup i have put the table in the same spreadsheet. since i don't want to copy this table to 20 spreadsheets i want to know if it's possible to have the table in a separate spreadsheet. thanks in advance for your help. your suggestions in the past have been VERY helpful. -- aprilshowers -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup Tables | Excel Worksheet Functions | |||
VLookUp Tables | Excel Worksheet Functions | |||
VLOOKUP with TABLES | Excel Discussion (Misc queries) | |||
VLookUp / 2 Tables | Excel Discussion (Misc queries) | |||
VLookUp Tables | Excel Worksheet Functions |