Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference the value of a cell within a formula?
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference the value of a cell within a formula?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference the value of a cell within a formula?
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) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference the value of a cell within a formula?
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference the value of a cell within a formula?
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?? "Pete_UK" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I reference the value of a cell within a formula?
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 "masterbaker" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formula with cell reference decreasing automatically | Excel Discussion (Misc queries) | |||
how to paste formula but reference a fixed cell | Excel Discussion (Misc queries) | |||
Function/ formula to output a cell reference | Excel Worksheet Functions | |||
How do I leave formula cell blank if 2nd reference cell is empty? | Excel Discussion (Misc queries) | |||
copied formula has correct cell reference, but result of original | Excel Worksheet Functions |