Assuming the entry in A1 is a date perhaps:

Range("A2").Formula = "='c:\" & Format(Range("A1").Value, "m-d-yy") &
"\[test.xls]sheet1'!b7"



--
Jim Rech
Excel MVP
"Al" wrote in message
...
| I'd like to calculate a formula based on date values in
| column A. My final formula should read something like
| this ='c:\02-05-04\[test.xls]sheet1'!b7
|
| (02-05-04 is in cell a1)
| And it should link to a value referenced by the formula.
|
| Can this be done? Thanks in advance.