Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
CornNiblet
 
Posts: n/a
Default Using single cell reference as table array argument in Vlookup

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   Report Post  
KL
 
Posts: n/a
Default

=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   Report Post  
CornNiblet
 
Posts: n/a
Default

Thank you - this will save me a ton of time. Very much appreciated!!

  #4   Report Post  
BlueDaze
 
Posts: n/a
Default


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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel: hold a reference to a single cell when copying formulas? Gary Brown Excel Worksheet Functions 0 May 18th 05 05:50 PM
how to create a variable column in cell reference Sampson Excel Worksheet Functions 3 February 21st 05 11:13 PM
How do I reference one cell in a table by using both the row and . artland Excel Discussion (Misc queries) 3 February 18th 05 10:34 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM
name of another worksheet in cell for reference Tom A Johnson Excel Worksheet Functions 2 November 12th 04 12:28 AM


All times are GMT +1. The time now is 08:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"