Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing named ranges | Excel Discussion (Misc queries) | |||
Dynamic chart: Changing Ranges | Charts and Charting in Excel | |||
how do I use formulas with changing ranges in excel with macros | Excel Discussion (Misc queries) | |||
CountIF and changing ranges | Excel Worksheet Functions | |||
formula with changing cell ranges | Excel Worksheet Functions |