Results 1 to 9 of 9

20030624, 05:52 #1
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 199
 Thanks
 8
 Thanked 0 Times in 0 Posts
Referring to Filenames in Formulas (Excel 2K)
Hi,
I have a working formula which I am having difficulties with because I want to remove the filename referenced in it and store it separately for easier updating on an annual basis. The current working formula is:
=VLOOKUP(S3,'[Sowreg022003.xls]SOW Register'!$A$5:$D$200,4,FALSE)
I would like to separate the filename, or filename and sheetname and store them in a separate cell but am unable to get a reference similar to below to work satisfactorily:
Cell U2: "Sowreg022003.xls" (No Quotes)  Changes each year.
My initial attempts to use this have been along the lines of what is below:
Cell U4: =INDIRECT("'["&U2&"]SOW Register'!$A$5")
and this currently gives #REF!  an invalid reference.
Any suggestions as to how I can get this to work?
Regards,
Peter Moran
Two heads are always better than one!

20030624, 08:36 #2
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Referring to Filenames in Formulas (Excel 2K)
When using INDIRECT with an external file, the external file MUST be open.
Is the "sowreg022003.xls" file open when the other file is calling it?
Steve

20030624, 13:02 #3
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 199
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Referring to Filenames in Formulas (Excel 2K)
Yes, the file is open Steve. Thanks for the input.
Also altered the formula in the previous post from:
Cell U4: =INDIRECT("'["&U2&"]SOW Register'!$A$5")
to:
Cell U4: =INDIRECT("'["&U2&"]SOW Register'!"&$A$5)
Still getting #REF!
Do single quotes (apostrophes) have to be handled specially in strings?
Regards,
Peter Moran
Two heads are always better than one!

20030624, 13:31 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Referring to Filenames in Formulas (Excel 2K)
If you get rid of the INDIRECT what is your string? It sounds like you have a mistake in your string.
There is no problem with using single quote in a string.
I can NOT duplicate your problem. The formulas as you have posted them seem to be fine.
I created a file named "Sowreg022003.xls"
with a sheet named "SOW Register"
And a table of example things in $A$5:$D$200
I put a value from col A in S3 of a "test book" and got the proper result from:
=VLOOKUP(S3,'[Sowreg022003.xls]SOW Register'!$A$5:$D$200,4,FALSE)
I put in cell U2 of test book the filename:
Sowreg022003.xls
Then used the formula in U4:
=INDIRECT("'["&U2&"]SOW Register'!$A$5")
and retrieved the contents of cell A5 from sheet "SOW Register" in filenamed: "Sowreg022003.xls"
as expected:
If I use your "new" change:
=INDIRECT("'["&U2&"]SOW Register'!"&$A$5)
I need to put a cell reference in cell A5 of the testbook to pull up a cell
If I put the text:
"D4" in cell A5 of the test book I retrieve the contents of cell D4 from sheet "SOW Register" in filenamed: "Sowreg022003.xls"
If I do NOT have anything in A5 or it is NOT a valid cell reference, I will get the #ref! error. If the file "Sowreg022003.xls" is closed, you will also get the #ref! error since indirect will NOT work on closed files.
The VLOOKUP formula (which is a DIRECT reference) will work fine even if "Sowreg022003.xls"
is closed!
Steve

20030624, 13:36 #5
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 199
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Referring to Filenames in Formulas (Excel 2K)
For anyone interested I have found a solution:
U4: =INDIRECT("'["&U2&"]SOW Register'!$A$5")
Note that the cell address at the end of the string must be inside the quotes  I can't see any other solution that works.
Peter Moran
Two heads are always better than one!

20030624, 13:50 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Referring to Filenames in Formulas (Excel 2K)
$A$5 can be outside the quotes:
=INDIRECT("'["&U2&"]SOW Register'!"&$A$5)
but then, of course the INDIRECT kicks in  it evaluates the contents of cell A5 on the current sheet and concatenates this with the string before it. So if U2 contains "Sowreg022003.xls" and A5 contains "P3", the formula will return the contents of '[Sowreg022003.xls]SOW Register'!P3, and if A5 contains 17, the formula will return #REF since it tries to return the contents of '[Sowreg022003.xls]SOW Register'!17 which is not a valid address.

20030624, 14:03 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Referring to Filenames in Formulas (Excel 2K)
I am confused:
What is different about this NEW one from <post#=267846>post 267846</post#>:
=INDIRECT("'["&U2&"]SOW Register'!$A$5")
and the one from your ORIGINAL <post#=267750>post 267750</post#>
=INDIRECT("'["&U2&"]SOW Register'!$A$5")
They both look the same to me.
As I mentioned in my <post#=267842>post 267842</post#> (and Hans reiterated in <post#=267851>post 267851</post#>, the formula with the cell OUTSIDE the reference works fine:
=INDIRECT("'["&U2&"]SOW Register'!"&$A$5)
though you have to (in the file with this formula, have a CELL REFERENCE in cell A5 since it is INDIRECTLY referenced (just like the filename is indirectly referenced)
Steve

20030624, 22:13 #8
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 199
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Referring to Filenames in Formulas (Excel 2K)
Hi Steve and Hans,
Thanks for your input, most appreciated. I will try and clarify below.
I started with:
=VLOOKUP(S2,'[Sowreg022003.xls]SOW Register'!$A$5:$D$200,4,FALSE)
and wanted to remove the filename from this and a number of other formulas and make it an external reference for more obvious updating at the end of a financial year.
Given some initial problems, I tried to simplify my problem and was eventually successful, thanks to your assistance, with:
=INDIRECT("'["&U2&"]SOW Register'!$A$5")
which had the external reference I wanted and showed me the contents of cell A5 in Sowreg022003.xls.
However I have been unable to successfully extrapolate that to my original formula, as neither:
=VLOOKUP(S2,INDIRECT("'["&U2&")]SOW Register'!$A$5:$D$200"),4,FALSE)  gives #REF!
nor
=VLOOKUP(S3,INDIRECT("'["&U2&")]SOW Register'!Register"),4,FALSE)  gives #REF!  with Register a range defined A5200.
will work as I want.
Any further ideas?
Many thanks,
Peter Moran
Two heads are always better than one!

20030624, 22:50 #9
 Join Date
 Jan 2001
 Location
 Melbourne, Victoria, Australia
 Posts
 199
 Thanks
 8
 Thanked 0 Times in 0 Posts
Re: Referring to Filenames in Formulas (Excel 2K)
Hi again,
Whoops  found my problem  incorrect string with a surplus ")" after &U2&".
=VLOOKUP(S2,INDIRECT("'["&U2&")]SOW Register'!$A$5:$D$200"),4,FALSE)
now:
=VLOOKUP(S2,INDIRECT("'["&U2&"]SOW Register'!$A$5:$D$200"),4,FALSE)
This now works exactly as intended.
Regards,
Peter Moran