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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

masterbaker - 25 May 2006 22:36 GMT

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)

Reply to this Message

Pete_UK - 25 May 2006 22:46 GMT

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

Reply to this Message

masterbaker - 26 May 2006 00:21 GMT

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

> You can use INDIRECT to do this, but the other file must be open at the
> same time. Try this:
[quoted text clipped - 7 lines]
>
> Pete

Reply to this Message

Elkar - 25 May 2006 22:59 GMT

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
[quoted text clipped - 5 lines]
>
> (lookup cell A5 within the file named File#1, which is the value in cell A1)

Reply to this Message

masterbaker - 25 May 2006 23:39 GMT

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.
>
[quoted text clipped - 12 lines]
> >
> > (lookup cell A5 within the file named File#1, which is the value in cell A1)

Reply to this Message

Pete_UK - 26 May 2006 00:14 GMT

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

Reply to this Message

Elkar - 26 May 2006 00:21 GMT

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:
[quoted text clipped - 33 lines]
> > >
> > > (lookup cell A5 within the file named File#1, which is the value in cell A1)

Reply to this Message