PDA

View Full Version : Solved: Hundreds of links on Multiple Sheets


mailman
08-12-2008, 07:55 AM
Hi again everyone!

I've got a few Workbooks with about 600 references per WorkSheet (6 worksheets) to another Workbook.

Each cell (of the 600 references) are running an HLOOKUP to the master Workbook which is all fine and dandy until I have to create a new WorkBook for the following month.

At that point I'm running a Search/Replace for the full file path (b/c the previous month goes to an archive folder). This takes FOREVER!!!! ;)

My question is, what is the syntax for each cell for me to store the path on a separate worksheet and concatenate it with the cell location on the other worksheet?

For example (I'll use a simpler version of what I'm trying to do):
Book1 has a reference in A1 to Book4 as follows:


='C:\Documents and Settings\user10\Desktop\[Book4.xls]Sheet1'!A1


Book4 just contains a string "Hello world".

I'd like Book1 to link to Book4 as follows:
Book1 has the path in A2:


C:\Documents and Settings\user10\Desktop\[Book4.xls]


So, my reference in Book1!A1 would be something like this:


=A2 & Sheet1'!A1


I've tried this, example, but it's not working for some reason. Is this not doable, or is my syntax all screwy?
Bob Phillips
08-12-2008, 09:22 AM
There isn't an Excel built-in answer.

Normally you would use INDIRECT (INDIRECT("'"&A1&!Sheet1'!A1")), but INDIRECT fails on a closed workbook. Laurent Longre has an XLL called MoreFunc that can address this, and Harlan Grove has posted a solution called PULL, but nothing built in as I say.

More fool you for linking to other workbooks is what I say, it is, always has been, a problem.
mailman
08-12-2008, 11:47 AM
Gotcha xld, thanks again.

btw.. Didn't catch that last sentence:


More fool you for linking to other workbooks is what I say,


Were you suggesting to NOT link to other workbooks?
Bob Phillips
08-12-2008, 02:23 PM
Absolutely, far more trouble than it is worth in my opinion.

Three things I avoid like the plague
#1 - shared workbooks
#2 - linked workbooks
#3 - merged cells.
mdmackillop
08-12-2008, 02:46 PM
Absolutely, far more trouble than it is worth in my opinion.

Three things I avoid like the plague
#1 - shared workbooks
#2 - linked workbooks
#3 - merged cells.
Is this in order of avoidance?
Bob Phillips
08-12-2008, 04:10 PM
No, they all get avoided in the same order, more an order of rubbishness of implementation.