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)
|