How do I find any cells that reference a link

sarlo00

Board Regular
Joined
Nov 13, 2002
Messages
105
In the Links... Box, is there a way to find what cells contain a certain link? I checked the Links... Box in a new file and I cannot find a cell that uses one of the links listed in the box. Is there a way to check if there is even any cells used? Is there a way to list all the cells that reference a certain link?

Brian
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
Hi, select the cell in question and select Tools|Formula Auditing|Trace Dependents.
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460
I ahve 2 methods that I use to find rogue links:

1. Select all worksheets, and then find (Ctrl-f) formulas that contain the link. You can then change the formula to remove the link, or re-link (ensure that you deselect all other worksheets)

OR

2. Change the link to the current spreadsheet and see what falls over. The danger in this is that if this creates illogical references, you will to press "Esc" once for each reference, so try option 1 first.

These methods are a little nasty but seem to work.

:)
 

sarlo00

Board Regular
Joined
Nov 13, 2002
Messages
105
parry,
Not exactly what I meant, but thanks.

tactps,
Thanks. I never thought to select a sheet and FIND on the name of the link. Thanks.

Brian
 

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174

ADVERTISEMENT

Another way:

Goto Tools>Options>General Tab

And check the Formulas box

This will display the formulas as typed, i.e.

=[Book1]Sheet1!$C$11

Very handy if you can't find that bleedin link that keeps on trying to update.

Regards
 

sarlo00

Board Regular
Joined
Nov 13, 2002
Messages
105
You know ... I don't have a Formula Box in Tools>Options>General Tab. I'm running Excel 2000. Maybe that's why. Is what you've described the same as Ctrl+~?
 

tactps

Well-known Member
Joined
Jan 20, 2004
Messages
3,460

ADVERTISEMENT

In Excel 2000, it is on the "View" tab, not "General" tab.

:biggrin:
 

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
I'm running XP and 97 both have it.

Is it not under the >Window options?
The short-cut I use is CTRL+`
 

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
And it is under the View Tab, sorry!
Not looking properly

Any good to you?
 

sarlo00

Board Regular
Joined
Nov 13, 2002
Messages
105
So it is the same as selecting the entire sheet and pressing Ctrl+~.
Thanks for the direction tactps.

Brian
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,542
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top