Update links when opening Excel workbook

[krinid]

I'm sure there's most likely tons of threads on this topic, but a search on "links" or "update links" returns too many hits and I can't find anything useful in the myriad - if anyone knows of a relevant thread, please advise.

How can I find out what cells are linked to other workbooks?

Using Edit/Link Settings, I'm able to get a list of the references and cancel them, etc, but all it lists it what workbooks are being referenced. Is there a way to find out exactly which cells from the active book are linked to which cells in the linked book?

 

[Dreamboat]

The only way I'm aware of is a little product called Link Hacker available at

http://www.mrexcel.com/sunshop/index.php?action=item&substart=0&id=11

Otherwise, it'd take a coder hours to create the capability.

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[krinid]

Dreamboat,
hmmm, I can't expense the cost of the link hacker add-in, so I guess I'll have to spend hours at it if it becomes a problem. Any idea how to begin detecting these links? Do I have to go through every cell and check it's contents and format?

 

[Dreamboat]

Hm. Do a search on google for findlinks.xla and you'll get some german sites. Anyway, I would think that you could use that file to find links too. I just don't know how they'll be reported to you.

And remember, if you make $10 an hour, then $50 is only 5 hours. I'm not trying to sell you the product. While I had a great deal to do with it's development (as project manager), I don't get any royalties or anything. I just happen to know it works REALLY FREAKING COOL. LOL

Seriously, it tells you everything, and allows you to change them too, in the event that you would move files around.

I'm not a coder (like I said, I do project management), so I don't have the first idea of how to code something like that. Sorry.

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[Dreamboat]

Just wanted to say...I used to be I'd never suggest a product that I was involved in creating. But I know that if I was not involved, but still knew the product, I would still recommend it. So why not recommend it?

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[krinid]

Now when viewing Edit/Link Settings I can't cancel the link - it's grayed out... even without knowing where the link was, before I could disable the link (forcing it to become a value). What condition causes this to gray out? (And how I can cause it to be available again?)

 

[krinid]

The only link I found among the the German sites was

http://www.bmsltd.co.uk/

which appears to be down (under construction is the only page showing).

No problem recommending products, whether you had a part in it or not - if it's a good product, it deserves recommendation. However, I feel it's kind of expensive for such a specific tool. I find that with a bit of leg work and guidance, one can usually accomplish a specific task themselves, and be better off for having the know how of the process, too, although likely falling short of final frills, bells and whistles that a purchased product may have. (I guess what I'm getting at is a variation on the old fisherman's metaphor, that I'd rather learn to fish than go to the supermarket to buy some).

That said, I decided to look in the least likely spot to find help - Excel Help, and it actually contained what I was
looking for:

Find links in a workbook
There is no automatic way to find links used in a workbook. However, because links use brackets [ ] to enclose the source workbook name when it is closed, you can search for it. For example =SUM([Budget.xls]Annual!C10:C25).

Close all workbooks except the one you want to find the links in.
On the Edit menu, click Find.
Click Option.
In the Search Text box, enter [.
In the Search area box, click Book.
In the Search target box, click Formulas.
Click Find All.
In the box at the bottom, look in the Formula column for formulas that contain [.
To select the cell with a link, select the row in the box at the bottom.
Links are also commonly used in names, text boxes, or chart titles.

 

[Dreamboat]

krinid: I went back over your post and am having a good chuckle right now over how STUPID you must think I am!!

For whatever reason, I thought you wanted to do this in MANY workbooks. Your first post says workbookS two times. But that's correct, and MY BAD. LOL

I'm really sorry for wasting your time! Please forgive me. After all...it's the day after Easter. Hee hee.

Anne Troy

http://www.amazon.com/exec/obidos/tg/detail/-/0972425845/104-9778384-2391158?v=glance&s=books

 

[krinid]

Anne,
Not at all; after all, you're published AND a project manager, so by definition you can't be stupid

Seriously though, I think your understanding was a valid interpretation of "How can I find out what cells are linked to other workbooks?"; I meant "How can I find out what cells in a given workbook are linked to other workbooks?"

For multiple workbooks, a macro could use this technique and perform searches in each book and report the results. I think I might just make that macro - it'd probably come in useful.

Btw, I always assumed you were a programmer!

 

[TonyJollans]

Hi krinid,

A macro to do this wouldn't be too hard to write, but a couple of points, if I may:

1. There should be no need to close linked-to workbooks in order to do the search. Whether they are open or closed affects whether or not the Path is included in the reference in the Formula, not whether or not the Workbook name is bracketed.

2. Brackets may exist in formulas which are not links. It is far less likely for a complete bracketed workbook name to appear in another context (except, perhaps, in INDIRECT references), so searching for an explicit name would be a better option. You can find the names by looking at the LinkSources collection. Some basic code could look like this ..

[blue]For Each strLink In ActiveWorkbook.LinkSources

    strBook = "[" & Mid(Link, InStrRev(strLink, "\") + 1) & "]"

    [green]' Do your Find loop here[/green]

Next[/blue]

I'm sure you are competent to extract a list of cell references using Find



Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[methoni]

I have used the find/replace option to find cells that are linked to other workbooks. Just use the workbook name in the "Find what" box (including the extension) and keep repeating. That is fairly simple if the link is in only a few cells and I know it works.

 

[JICDB]

I'm kind of a novice at Excel and not sure if I understand the question correctly - but if you press Ctr+~ (tilda key) you will see the contents of each cell including calculations and linked cells.