VLOOKUP, changing ranges
 

Hi there,
I am using multiple Vlookups in one sheet. There is a vlookup in each column
which retrieves data from multiple file locations. So for example, column B
looks at column A, and then goes to File 1 to look for matching data. Column
C then looks at column A, and then goes to File 7 to find matching data etc
etc. This is rather tiresome as I have 30 or so columns going to the same
number of files to look for matching data. I also have various if / iserror
commands around the vlookups so I effectively have to manually set the range
for each vlookup each time. Is there some way I can build a list of all the
range names in the output file and have the vlookups reference that, rather
than setting the range for each column each time?
Thanks!

VLOOKUP, changing ranges
 

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help you.

Sarah wrote:

Hi there,
I am using multiple Vlookups in one sheet. There is a vlookup in each column
which retrieves data from multiple file locations. So for example, column B
looks at column A, and then goes to File 1 to look for matching data. Column
C then looks at column A, and then goes to File 7 to find matching data etc
etc. This is rather tiresome as I have 30 or so columns going to the same
number of files to look for matching data. I also have various if / iserror
commands around the vlookups so I effectively have to manually set the range
for each vlookup each time. Is there some way I can build a list of all the
range names in the output file and have the vlookups reference that, rather
than setting the range for each column each time?
Thanks!

--

Dave Peterson