Inculde a cell value in a formula link

[shadow12345]

I have the below formula in a sheet, all it does is copies values into a summary sheet. There are around 40 of these linking to different points for each day. The file rubber position is saved daily in the format i:\rubber position dd.mm.yy.xls

='I:\[RUBBER POSITION 16.06.08.xls]RUBBER POSITION'!$BP$205

which works fine, however what i want to do is to rewrite this formula to do the below

=='I:\[RUBBER POSITION (the value in cell b3 the date im trying to target in the formula) xls]RUBBER POSITION'!$BP$205

DU REPORT 2008.xls
	A	B	C	D	E	F
3		18-Feb	19-Feb	20-Feb	21-Feb	22-Feb
4
5	SMR 5	63.00	18.90	18.90	18.90	18.90
6	SMR/TECH10	-246.93	-226.77	-226.77	-65.49	-65.49
7	TSR 10	2267.60	2267.60	2267.60	0.00	0.00
8	SIR20	-1583.82	-1583.82	-1583.82	-1422.54	-1422.54
9	TSR 20	-472.50	-472.50	-472.50	-449.82	-449.82
week 08 2008

 

[Richard Schollar]

Hi

You either need VBA or the morefunc add-in to do this as the target file is closed. If it was open, you could use INDIRECT, but as soon as the file is closed these formulas would return #REF! errors

 

[shadow12345]

ok, what is the best way to replace using VBA? I looked at maybe trying find and replace ?

a start would do, i should be alright expanding the ranges and adjusting it and so on.

 

[Richard Schollar]

here's the VBA required:

http://www.j-walk.com/ss/excel/tips/tip82.htm

Note that the function cannot be called from a worksheet cell, you need to execute it thru VBA (which may not be ideal for you).

 

[shadow12345]

sorry, thats a little beyond me. Copy the function, got that part, how would i apply this into what i need to do?



as in, put the value into the formula?

=i\rubber position & getvalue (p f s a) . xls & rubberpostion bp205

something like that?