View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
masterbaker
 
Posts: n/a
Default How do I reference the value of a cell within a formula?

Thanks for the response but it's not quite what I am looking for. I will be
more specific:

I am trying to have my vlookup pull from an entire file (not just a sheet)
that will not be open. The name of the file it's pulling from will change
depending on the date. I want the table array value in the lookup formula to
reflect the value within a certain cell.

My current formula based on your suggestion (not working):
=VLOOKUP(N16,INDIRECT("'["&R18&"]"),26,FALSE)

where R18 =
C:\Documents and Settings\msmith\Desktop\[052506.xls]Sheet 1'!$A$2:$Z$26

I have a working hardcoded vlookup:
(VLOOKUP(N16,'C:\Documents and Settings\msmith\Desktop\[052306.xls]Sheet
1'!$A$2:$Z$26,26,FALSE)

I want the file named 062306.xls to changed depending on the value in R18
Still use the Indirect function??


"Elkar" wrote:

You'll need to use the INDIRECT function.

=VLOOKUP(A5,INDIRECT("'["&A1&".xls]Sheet1'!$A$1:$Z$20"),2,FALSE)

HTH,
Elkar


"masterbaker" wrote:

I am trying to have a Vlookup that pulls from another file. The issue is I
want the name of the file to be the value within a specific cell. For
instance, cell A1 has the value "File#1" (through a formula). I want the
vlookup to pull from File#1 without hard coding this into the formula.

Essentially I want my vlookup to be something like:
=VLOOKUP(A5,'[A1.xls]Sheet 1'!$A$1:$Z$10,2,FALSE)

(lookup cell A5 within the file named File#1, which is the value in cell A1)