in the formula bar i get - 'C:\mydata\Jan\[Jan_Data.xls]Sheet1'!AA15
and the value in C3 is the value from Jan_Data of sheet1 cell AA15 -
say 121
OK
what I want to do is put the basic path - C:\mydata\ - into cell A1
and put the month folder - in this case, Jan, into A3
and put the file name - in this case Jan_Data.xls - into B3

i would then enter the month - say, Feb - into A3
and the file name into B3
and enter a concatenation into C3
the formula would then look something like: =$A$1 & $A3 & $B3 &
"Sheet1'!AA15

however, it does not work

If you *carefully* compare the path refs you'll see the reason why your
concatenation doesn't work!

Take note (specifically) how the path ref that *does work* formats
construction of string; it begins with an apostrophe, wraps the
filename in square brackets, and closes the ref after the sheetname
with another apostrophe followed by an exclamation character and the
the range ref.

Construct your concatenation so it formats your string exactly the
same. IOW, the constant characters ('[]'!) *must* be arranged around
the variables (path,filename,sheetname) in the same way in front of the
range ref!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion