vlookup tables

  • Thread starter Thread starter april
  • Start date Start date
A

april

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.
 
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!)
 
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 said:
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!)

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.
Click to expand...
Click to expand...
 
Back
Top