Excel Link Problem

[jnix]

I have several workbooks linking to one source workbook. When I add or delete a line in the source workbook, I have to redo all the links in the other workbooks. Is there a way around this?
Thanks,
jnix

 

[GlennUK]

Define names for the cells which are to be linked, and then link to the defined name instead of a specific cell reference.

Once you've done this it will save you a ton of time, as it has done for me over the years.


On the other hand, if you have thousands of links already, and want the links to adjust as you are inserting or deleting rows in the source data, simply have all the dependent workbooks open in the same Excel session as the source data, and the links adjust automatically!!!!!!


Cheers, Glenn.

 

[rdodge]

The Defined names only works within the same workbook. It does not work for going over multiple workbooks as I have tried that strategy and it did not work for me. Not only that, but it is stated as such in the help files.

Example, if you named a range in Book1.xls with the name of "MyLineTotalRange"

and you tried to refer to that range via the name from within "Book2.xls" like:

=SUM('[Book1.xls]Sheet1'!MyLineTotalRange)

This formula would fail to return an expected result cause it's using a name to refer to an external workbook.

What I did to overcome this issue, I used indirect formulae to make my links more dynamic. Yes, the formulae tended to get rather ugly, but here's an example of one that I have done.

=ADDRESS(4,COUNTA(INDIRECT(ADDRESS(ROW()+1,MATCH("WEEK",INDIRECT(ADDRESS(ROW()+1,1)):INDIRECT(ADDRESS(ROW()+1,256)),0)+10)):INDIRECT(ADDRESS(ROW()+1,COLUMN())))+11)

Wish I had a better example that included external workbook reference, but what this example does show, how to use the various lookup and information functions.

Here's another example that I have used:

=VALUE(INDIRECT("'\\FileServer01\Organizations\Production\[Man hours.xls]Sheet1'!$B$"&COLUMN()-11))

As time went, the amount of data and formulae got to such an extent that not only was my data broken down to multiple workbooks to address the RAM usage issue of Excel, but eventually, the number of external links also became an issue to the point that I had to use VBA coding instead to gather the information. Now, I'm moving into the direction of moving a lot of my stuff into Access.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[GlennUK]

Hi rdodge,

thanks for voicing your concerns regarding using defined names to link external data.

I'd point out that I've been doing this for over a decade, but only using direct links ( i.e. names for individual cells ), with no problem, which would look something like this as a cell formula:
='C:\MyDocs\DataBook.xls'!MyDataCell

This is a nice simple way of ensuring that links stay in line, and retrieves data from closed external workbooks when updating links as prompted.


Cheers, Glenn.

 

[rdodge]

Actually, come to think about it, there is a way, and it must be the way you are talking about. However, there's a catch to it. The Name still must be within the workbook for this to workbook, but the internal name itself can refer to an external workbook. Examples:

If you setup a range in Book1.xls as the name of "MyDataRange" to refer to the range of "Sheet1!A5:M2342"

In Book2.xls, you still can't use the named range like:

VLOOKUP(A5,MyDataRange,2,False)

In order to have Book2.xls to refer to that same very range, you just be in Book2.xls, define the range name, set the name as "MyDataRange" and set the reference to:

"'C:\My Documents\[Book1.xls]Sheet1'!A5:M2342"

Then your named range would work.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.