How do I update links based on a cell value?

[Guest]

I'm trying to update links based on a cell value.
For Example: A1 thru A4 has the values 21,22,23, and 24 respectivly. My
link in B2 is ='C:\temp\[Cash Report 12-22-05.xls]Sheet1'!$D$5.

Is there a way that I can have the link in B2, change to 12-23-05(A2's
Value) without doing a manual find and replace for each cell? If there is a
macro way to do this that would be great too.

Thanks in Adavance!

Vick

 

[Dave Peterson]

If the resulting file name will be open, you can use =indirect(). But that will
fail if the "sending" workbook is closed.

How about just
Edit|Links|change source?

I'm trying to update links based on a cell value.
For Example: A1 thru A4 has the values 21,22,23, and 24 respectivly. My
link in B2 is ='C:\temp\[Cash Report 12-22-05.xls]Sheet1'!$D$5.

Is there a way that I can have the link in B2, change to 12-23-05(A2's
Value) without doing a manual find and replace for each cell? If there is a
macro way to do this that would be great too.

Thanks in Adavance!

Vick

 

[Guest]

are there anyway to work around the limitation that the sending file needs to
be open?

regards
Gerrit

If the resulting file name will be open, you can use =indirect(). But that will
fail if the "sending" workbook is closed.

How about just
Edit|Links|change source?

I'm trying to update links based on a cell value.
For Example: A1 thru A4 has the values 21,22,23, and 24 respectivly. My
link in B2 is ='C:\temp\[Cash Report 12-22-05.xls]Sheet1'!$D$5.

Is there a way that I can have the link in B2, change to 12-23-05(A2's
Value) without doing a manual find and replace for each cell? If there is a
macro way to do this that would be great too.

Thanks in Adavance!

Vick

 

[Dave Peterson]

Check your other post for more replies.

are there anyway to work around the limitation that the sending file needs to
be open?

regards
Gerrit

If the resulting file name will be open, you can use =indirect(). But that will
fail if the "sending" workbook is closed.

How about just
Edit|Links|change source?

I'm trying to update links based on a cell value.
For Example: A1 thru A4 has the values 21,22,23, and 24 respectivly. My
link in B2 is ='C:\temp\[Cash Report 12-22-05.xls]Sheet1'!$D$5.

Is there a way that I can have the link in B2, change to 12-23-05(A2's
Value) without doing a manual find and replace for each cell? If there is a
macro way to do this that would be great too.

Thanks in Adavance!

Vick