editing formula

chaju · Mar 26th, 2002, 12:14 AM

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 · Mar 26th, 2002, 01:01 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 12:14 AM	#1
chaju Board Regular Join Date: Mar 2002 Posts: 82	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!!

Mar 26th, 2002, 01:01 AM	#2
paelinck Board Regular Join Date: Mar 2002 Location: Leiden, The Netherlands Posts: 68	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