VLOOKUP, changing ranges

[Guest]

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]

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.