Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Is it possible to use a cell reference (on the same worksheet as the
vlookup function) as the table array argument in the vlookup function in place of an explicit table array argument? I'm trying to build a simple reporting tool that would allow me to specify the file location, file name, worksheet name and array, and then have the vlookup function recognize these arguments as the "table array". In other words, Here's the typical vlookup: VLOOKUP(D10,Sheet2!$A$1:$P$100,5,FALSE) What I want to do is to be able to replace the "Sheet2!$A$!:$P$100" part of the formula with reference to a single cell on the same sheet as the Vlookup formula. This cell would contain the information on the table array to use (using the concatenate formula or something equivalent). The situation is that I have several different versions of a very large file with 50+ tabs. I need to be able to specify the file name and the tab name in order to extract data from these files quickly. Something more elegant than doing a find & replace each time. Thanks in advance! |
#2
![]() |
|||
|
|||
![]()
=VLOOKUP(D10,INDIRECT(A1),5,FALSE)
will work as long as the remote file is open. Regards, KL "CornNiblet" wrote in message oups.com... Is it possible to use a cell reference (on the same worksheet as the vlookup function) as the table array argument in the vlookup function in place of an explicit table array argument? I'm trying to build a simple reporting tool that would allow me to specify the file location, file name, worksheet name and array, and then have the vlookup function recognize these arguments as the "table array". In other words, Here's the typical vlookup: VLOOKUP(D10,Sheet2!$A$1:$P$100,5,FALSE) What I want to do is to be able to replace the "Sheet2!$A$!:$P$100" part of the formula with reference to a single cell on the same sheet as the Vlookup formula. This cell would contain the information on the table array to use (using the concatenate formula or something equivalent). The situation is that I have several different versions of a very large file with 50+ tabs. I need to be able to specify the file name and the tab name in order to extract data from these files quickly. Something more elegant than doing a find & replace each time. Thanks in advance! |
#3
![]() |
|||
|
|||
![]()
Thank you - this will save me a ton of time. Very much appreciated!!
|
#4
![]() |
|||
|
|||
![]() Or use Index. =index(a1,1,1) -- BlueDaze ------------------------------------------------------------------------ BlueDaze's Profile: http://www.excelforum.com/member.php...o&userid=27465 View this thread: http://www.excelforum.com/showthread...hreadid=469680 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Excel: hold a reference to a single cell when copying formulas? | Excel Worksheet Functions | |||
how to create a variable column in cell reference | Excel Worksheet Functions | |||
How do I reference one cell in a table by using both the row and . | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions | |||
name of another worksheet in cell for reference | Excel Worksheet Functions |