Can I use TEXT command in workbook link?

[Brett]

Hello.

I'm trying to create links within one workbook to several hundred others.
I'd like to use the TEXT formula in my cell references (about 10 per
workbook) so that I can simply copy all of my workbook names to update the
cell references instead of using the REPLACE function on all of my links.

Basically I will be using a cell value as part of the name of a linked
workbook.

For example:

In Workbook_A:

Workbook_1 Link1 Link2 Link3 Link4 Link5
Workbook_2 Link1 Link2 Link3 Link4 Link5
....
Workbook_400 Link1 Link2 Link3 Link4 Link5

 

[Conan Kelly]

Brett,

How are you entering your hyperlinks?

In XL, there is a HYPERLINK() function where you would create a formula to
create you hyperlinks. If you know how to concatenate, you could use this
function to create a formula the will return a hyperlink in the cell.

Be sure to look up HYPERLINK() in XL's Help, it is a little tricky to use.

HTH,

Conan

 

[Brett]

Hi Conan

For example, my cell reference in Book2:

=[Book1]Sheet1!$C$18

I would like to find a way to replace "Book1" with TEXT(A1,"")

That way I can copy and paste all of my workbook names into cells A1 to A400
in Book2 and the formulas will all update. Otherwise I need to do a Find /
Replace on each line of formulas to replace Book1 with Book2 and Book3, etc.
Or I need to open 400 workbooks and Copy / Paste Special / Paste Link the
data.

Brett

 

[Conan Kelly]

Brett,

Pardon me, I've misunderstood your original post.

Yes it is possible, but there are some issues:

This can be done using the INDIRECT() function, but the problem is all of
your other workbooks need to be open in order for the values to be updated
in your master workbook. If you have 400+, there is no way you will be able
to open all of them at once.

If this is something that only needs to be set up once (you won't be adding
links to other books or other cells in existing books), then there is a
work-around that we can do.

In the formula you provided, it is referencing cell C18 in another book. If
you copy this down, will it reference the SAME CELL (C18) in a different
workbook? If you copy it accross, will you need it to reference cells D18,
E18, F18, etc...?

Conan

Hi Conan

For example, my cell reference in Book2:

=[Book1]Sheet1!$C$18

I would like to find a way to replace "Book1" with TEXT(A1,"")

That way I can copy and paste all of my workbook names into cells A1 to
A400
in Book2 and the formulas will all update. Otherwise I need to do a Find
/
Replace on each line of formulas to replace Book1 with Book2 and Book3,
etc.
Or I need to open 400 workbooks and Copy / Paste Special / Paste Link the
data.

Brett

Brett,

How are you entering your hyperlinks?

In XL, there is a HYPERLINK() function where you would create a formula
to
create you hyperlinks. If you know how to concatenate, you could use
this
function to create a formula the will return a hyperlink in the cell.

Be sure to look up HYPERLINK() in XL's Help, it is a little tricky to
use.

HTH,

Conan