Link multiple workbooks to one (hyperlink & links)

**Xx7** · 03-23-2011, 12:38 PM

I have one workbook called INDEX and 100 other workbooks.

I want A1 in "other workbook 1" to link to A1 in INDEX, B1 in "other workbook 1" to link to B1 in INDEX, C1 in "other workbook 1" to link to C1 in INDEX.

...then repeat this for each workbook on the subsequent row of INDEX, so...

I want A1 in "other workbook 2" to link to A2 in INDEX, B1 in "other workbook 2" to link to B2 in INDEX, C1 in "other workbook 2" to link to C2 in INDEX.

I want A1 in "other workbook 3" to link to A3 in INDEX, B1 in "other workbook 3" to link to B3 in INDEX, C1 in "other workbook 3" to link to C3 in INDEX.

thx

**6StringJazzer** · 03-23-2011, 03:06 PM

What is the naming convention for the 100 workbooks?

**Xx7** · 03-23-2011, 06:26 PM

It will just be a bunch of copied workbooks. eg. names like...

Book(1)
Book(2)
Book(3)
.
.
.
Book(100)

**6StringJazzer** · 03-23-2011, 07:45 PM

If the number of the workbook is available in parentheses, then I would add a second sheet in each numbered workbook to put this formula in Sheet2!A1

=CELL("filename")

and put this in A2

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

Make A2 a named range, let's use the name MyNum.

Then back on Sheet1, use this formula in A1, then copy as far to the right as desired:

='[INDEX.xls]Sheet1'!INDEX(COLUMN(),MyNum))

(Don't confuse the INDEX function with your coincidentally named file.)

**Xx7** · 03-26-2011, 05:18 PM

In column A in my summary sheet I have:

**in Cell A1** ='C:\Users\BD\Desktop\Linked\[Book - Copy (1).xlsx]Sheet1'!$A$1
**in Cell A2** ='C:\Users\BD\Desktop\Linked\[Book - Copy (2).xlsx]Sheet1'!$A$1
**in Cell A3** ='C:\Users\BD\Desktop\Linked\[Book - Copy (3).xlsx]Sheet1'!$A$1
**in Cell A4** ='C:\Users\BD\Desktop\Linked\[Book - Copy (4).xlsx]Sheet1'!$A$1

...etc

How can I copy the formula down so that it will change the name of the workbook for each row? I want to change the name of the workbook to go from "Copy (1)"... to "Copy (2)"... to "Copy (3)".... all the way to "Copy (100)". I don't want to manually go into the cell and change the name.

**6StringJazzer** · 03-26-2011, 06:34 PM

That is the exact opposite of what you said you wanted in your first post. You described each workbook having references to a single INDEX workbook. In your last post you describe having a single workbook with references to numerous other ones.

The way to do this with formulas will not work well for you because it will require the use of the INDIRECT function, which requires each referenced external file to be open to work. I imagine that is unworkable with 100 workbooks.

**in Cell A1**

Please Login or Register  to view this content.

and copy down

The alternative is to use a VBA solution to populate those cells:

Please Login or Register  to view this content.

However, note that the referenced files should exist first. Each formula will be evaluated by Excel as it is populated into the cell, and if the referenced file does not exist, Excel will prompt to locate the file.