VLOOKUP across multiple workbooks

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have inherited a problem. I use Excel 2003. I work with two workbooks,
both having multiple worksheets (tabs). The first workbook is the Report
Master.xls. Data is downloaded from an outside service into one tab called
“Input.†The data from Input is then pulled into other worksheets by formula
and links with calculations to create a variety of different reports. This
all works the way it should.

The issue involves the second workbook (Expense Stats.xls). It consists of
forty worksheets. These are identical in structure and have unique tab names
(example: “Small Growthâ€). The VLookup formula cited below is in the cells
of a column on the Report Master workbook and calls for data from the second
worksheet (Expense Stats.xls). This also works.

=VLOOKUP($A$3,'C:\My Documents\[expense stats.xls]Small
Growth'!$C$2:$G$2000,5,FALSE)

My problem is that I must manually edit the worksheet reference in this
formula to point to a different tab (“Large Growthâ€, “Mid Value†and so
forth). The Input worksheet does have a column which references these values
and the Summary worksheet also has a reference to the needed values.

I cannot figure out how to edit the formula. Any assistance would be
appreciated and thank you.
 
Hi JackieW,

This describes one solution using the indirect function (which requires
both workbooks to be open to work):

http://tinyurl.com/jxvdt

Give it a go & report back if you have trouble (or if you succeed would
be nice too)
 
Back
Top