Cell value substitution in formula !

Hello all,

I have a consolidate xls file having the formula to some related cell, say A1, as follows:

='A:\[0501QQxls]Sheet1'!A1+'A:\[0501RR.xls]Sheet1'!A1

where 0501 is yymm every month, QQ & RR represent 2 companies in 2 separate files.


I want to input a cell, say D9, with text value '0501 in the cell.

The question is:
Can I select the cell value of D9 in the above A1 formula ?

So that I can change the value of D9 to 0502 and the formula can change to:

='A:\[0502QQxls]Sheet1'!A1+'A:\[0502RR.xls]Sheet1'!A1


Thanks for any valuable suggestions.

try this -

=INDIRECT("'A:\[" & D9 & "QQxls]Sheet1'!A1") + INDIRECT("'A:\[" D9 & "RR.xls]Sheet1'!A1")

This will only work if the workbooks being called are open. You could use a custom function called PULL() but this is slow if you are getting a lot of info or searching through a big range. The code can be found here
http://groups-beta.google.com/group/...2coff=1&rnum=7
and it needs to be pasted into the VB Editor.
It works in the same way as INDIRECT but it doesn't need the workbook to be open.

Hope this helps!

Hello Richardeye,

Thanks so much for your help.
My problem is solved !

Edward