Tracing Links

Topic

New Reply

I would like to know if Excel has a feature that will highlight/point out where a link is embedded in a spreadsheet? If not, any other ideas?

Reply | Quote

Replies

If you don’t have too big an area to examine, you could try Tools / Options / View and check on the Formulas box. That would allow you to do a quick visual examination of all formula cells to look for external references. Other than that, there are a couple of VBA tools around that will locate / list links. I don’t have the references handy at the moment.

Reply | Quote

Thanks, GeneO, Iwas aware of the latter methon; Iwas just looking for a “quick fix”!

Reply | Quote

I use the tedious process of searching for the link name. I’m looking for a better way.

Reply | Quote

Quick and dirty: Edit|Find “!” or, if you are sure links do not refer to named cells, “!$”, then keep hitting F3.

If used regularly/need to document it: IMO only with a VBA sub.

Reply | Quote

“VBA sub” (a novice here)?

Reply | Quote

Edited by gwhitfield on 01/01/11 08:30.

It’s an Excel “macro”. I differentiate between VBA functions and subs.

Reply | Quote

Edited by gwhitfield on 01/01/11 08:38.

Sorry-I am truly a novice at this and don’t know what you mean by “VBA”.

Reply | Quote

Hi,

A “VBA macro” is a routine which can be run behind Excel (or Word or other applications) which can do all sorts of usefule things for you. It may be to the spreadsheet (or Word document); it may just pop up windows and do nice things for you without doing anything to Excel.

It’s a programming language behid Excel, with the programming language based on Visual Basic (VB). As applied to an application like Excel, it becomes Visual Basic for applications (VBA).

What does all that %$# mean?

OK, in its most simple form, you can automate a repetitive task- that’s what the earlier postings were saying. So you can click on Tools, Macros, record macros- and then any actions you perform are recorded in “VBA” code. When you’ve finished your sequence of actions, click on Tools, Macros, Stop reording; if you then press on Tools, Macros, Macros, then select the macro you have just recorded, it will repeat the actions.

That’s it’s most simple form.

You may find that you have requirements to do much more than what you can achieve just recording and replaying. You can tweak what you’ve recorded; you can add functionaltity; you can build whole applications using what is actually a very powerful language.

But you probably don’t need to worry about those bits. Just the “Record” and “Play” functionality should help you in what you want to do.

My apologies for changing the previous 2 posts- there was obviously a misunderstanding there.

Reply | Quote

Augh! I do extensive and sometimes complex search-&-replaces, but I never thought to search for “!” alone. Thanks!

(Bangs head on desk, begins muttering passive-aggressively.)

Reply | Quote

Also, you could try using Go To (F5) and pressing the Special button, and look for Formulas. If you were doing a lot of this, I’d customize the toolbar.

Reply | Quote

How do you use Goto, Special for links?

Reply | Quote

Well a link is a formula. GoTo Special, lets you look specifically for formuals. You’ll also find regular formulas as well – can’t get away from that.

Reply | Quote