indirect function referenceing changing file name
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
|