Editing Formula daily problem

[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 copy the B1 formula to all rows in Column B 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!!

[BabyTiger]

Hi,

I think for this purpose, you might want to use a macro, codes as follow:

For i = 1 To Range("A1").End(xlToRight).Column
Range("B1").Offset(0, i - 1).Formula = "='c:My Document[Valuation " & Format(Range("A1").Offset(0, i - 1), "dd-mmm-yy") & "]Ingenium'!$E$4"
Next i

Note, for the file directory, its is 1 slash each time, instead of 2.

And "Range("A1").End(xlToRight).Column" is the last column of continueous data on row "A", ie. when you selected cell A1, then press ctrl-"right arrow". (this is to capture all the columns in row A. Alternatively, can replace that with a number(depends how many column you want to enter the formula in row B for).

HTH

[Yogi Anand]

On 2002-03-25 20:52, chaju wrote:
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 copy the B1 formula to all rows in Column B 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!!

Hi chaju:
BabyTiger has given you a macro solution. I give you below a formula that you can you can use as well. Here goes

="'c:\My Document\[Valuation"&TEXT(A1,"dd-mmm-yy")&".xls]Ingenium'!$E$4"

so if you have 25-Mar-02 in cell A1, and 26-Mar-02 in cell A2, applying the formulas will give in cells B1 and B2 ...

'c:\My Document\[Valuation25-Mar-02.xls]Ingenium'!$E$4
'c:\My Document\[Valuation26-Mar-02.xls]Ingenium'!$E$4

HTH
Please post back if it works for you ... otherwise explain a little further and let us take it from there!

[Yogi Anand]

Hi chaju:
Please not that this board doubles up on the use of the (slash caharacter) ... so you can adjust this aspect or any thing else to suit!

[chaju]

Thanks,, but I am not looking for solution to display the formula...
I am looking for the data linked with those file name (with corresponding date)

Any further help.. many many thanks