editing formula

[chaju]

Hi, It would be big help if anyone can help me with this problem

Suppose in Cell A1 I have date 25-Mar-02
And in Cell B1 I have fomula:
='c:My Document[Valuation 25-Mar-02.xls]Ingenium'!$E$4

NOTE: The file name has the date within to correspond with Cell A1.
The problem here is suppose I have to fill the B1 formula to B2,B3.... etc in and ensuring the file name within each formula has the date correspond to the date in the same row in Column A

Such as Cell A2: 26-Mar-02
So Cell B2:='c:My Document[Valuation 26-Mar-02.xls]Ingenium'!$E$4

So On...

Please help!!

 

[paelinck]

Hi,

I tried various possibilities with the INDIRECT function, but the workbook you refer to in the INDIRECT function must be opened to work. This is probably not what you want. I also tried creating a VBA function, but that didn't work either. The easiest way is to hard-code the reference to one specific workbook in cells B1, B2, etc. and to use the "Replace" function in the edit menu to change the date in the formulas. Not a very elegant solution, I agree. I guess the only other way is to write a short macro that fills the cells B1, B2 etc. with the right formula according to the value of A1.

I hope this helps.
Marc