How do I reference the value of a cell within a formula?

[masterbaker]

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)

 

[Pete_UK]

You can use INDIRECT to do this, but the other file must be open at the
same time. Try this:

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

I'm not sure why you have a range 26 columns wide when you are only
bringing data from the second column.

Hope this helps.

Pete

 

[Elkar]

You'll need to use the INDIRECT function.

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

HTH,
Elkar

 

[masterbaker]

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

You'll need to use the INDIRECT function.

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

HTH,
Elkar

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)

 

[Pete_UK]

If the file is open then you don't need the path to it - you only need:

'[filename.xls]Sheet name'!range

In your VLOOKUP you are putting square brackets around everything, but
these only need to go around the filename, so it won't work. If R18
contains "052306" and the file 052306.xls is open, then the formula
should be:

=VLOOKUP(N16,INDIRECT("'["&R18&".xls]Sheet 1'!$A$2:$Z$26"),26,FALSE)

Hope this helps.

Pete

 

[masterbaker]

Thanks for the response but I think I have an issue with the cell that the
indirect function is referencing. Even if I put INDIRECT(A1) I still get a
#ref error. The value in cell A1 is a contatenation. Would this cause it to
be a problem??

 

[Elkar]

If cell R18 contains the entire path, filename, sheet, and cell range, then
INDIRECT(R18) would be sufficient. However, the problem of needing the
target file open still exists.

I think you'll need a Macro solution here. Something like:

Sub Macro1()

Range("A1").Value = "=VLOOKUP(N16," & Range("R18").Value & ",2,FALSE)"

End Sub

I didn't know what cell you want the formula to appear in, so I just used
A1. Change to fit your needs.

HTH,
Elkar

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

You'll need to use the INDIRECT function.

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

HTH,
Elkar

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)