I need to get the value of a cell in a date stamped file. The file name will
change over time as the date stamp changes as below

cell A1 has file name without the date say MyFile
cell A2 has date in it say 05-Jan-07
so the actual file name would be A1&A2&.xls
cell A3 has sheet name in it say sheet1
cell A4 has cell reference in that file that i need say B6

so my formula looks like this
=INDIRECT(CHAR(39)&"["&A1&"]"&A2&".xls"&A3"&CHAR(39)&"!"&A4)
this produces a #REF I will be grateful if someone will point me in the
right direction as my syntax is obviously incorrect

Many thanks
--
with kind regards

Spike

Indirect won't work with a closed workbook.


http://makeashorterlink.com/?F2993260A

You could try Harlan Grove's PULL function. You can get it
ftp://members.aol.com/hrlngrv/ (look for pull.zip)

You would use it like so

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!"&"B5")

or

=pull("'"&A3&"\["&B3&".xls]"&C3&"'!B5")

or even

=SUMPRODUCT((pull("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&FirstRangeAddress)=whatever)
*pull("'C:\somedir\"&SubDir&"\["&Filename&"]"&
WorksheetName&"'!"&SecondRangeAddress))

As for alternatives, try the INDIRECT.EXT function in Laurent Longre's
MOREFUNC.XLL add-in, available from

http://xcell05.free.fr/english/

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Spike" wrote in message
...
I need to get the value of a cell in a date stamped file. The file name
will
change over time as the date stamp changes as below

cell A1 has file name without the date say MyFile
cell A2 has date in it say 05-Jan-07
so the actual file name would be A1&A2&.xls
cell A3 has sheet name in it say sheet1
cell A4 has cell reference in that file that i need say B6

so my formula looks like this
=INDIRECT(CHAR(39)&"["&A1&"]"&A2&".xls"&A3"&CHAR(39)&"!"&A4)
this produces a #REF I will be grateful if someone will point me in the
right direction as my syntax is obviously incorrect

Many thanks
--
with kind regards

Spike

Try replacing A2 with TEXT(A2,"dd-mmm-yy") otherwise it will be
interpreted as a serial number.


Spike wrote:
I need to get the value of a cell in a date stamped file. The file name will
change over time as the date stamp changes as below

cell A1 has file name without the date say MyFile
cell A2 has date in it say 05-Jan-07
so the actual file name would be A1&A2&.xls
cell A3 has sheet name in it say sheet1
cell A4 has cell reference in that file that i need say B6

so my formula looks like this
=INDIRECT(CHAR(39)&"["&A1&"]"&A2&".xls"&A3"&CHAR(39)&"!"&A4)
this produces a #REF I will be grateful if someone will point me in the
right direction as my syntax is obviously incorrect

Many thanks
--
with kind regards

Spike

many thanks to you both
--
with kind regards

Spike


"Lori" wrote:

Try replacing A2 with TEXT(A2,"dd-mmm-yy") otherwise it will be
interpreted as a serial number.


Spike wrote:
I need to get the value of a cell in a date stamped file. The file name will
change over time as the date stamp changes as below

cell A1 has file name without the date say MyFile
cell A2 has date in it say 05-Jan-07
so the actual file name would be A1&A2&.xls
cell A3 has sheet name in it say sheet1
cell A4 has cell reference in that file that i need say B6

so my formula looks like this
=INDIRECT(CHAR(39)&"["&A1&"]"&A2&".xls"&A3"&CHAR(39)&"!"&A4)
this produces a #REF I will be grateful if someone will point me in the
right direction as my syntax is obviously incorrect

Many thanks
--
with kind regards

Spike