Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

excel training microsoft - finding cells linked another

Forum home » Delegate support and help forum » Microsoft Excel Training and help » excel training microsoft - Finding cells that are linked to another spreadsheet

excel training microsoft - Finding cells that are linked to another spreadsheet

ResolvedVersion Standard

Andrea has attended:
Excel Intermediate course
Excel Advanced course

Finding cells that are linked to another spreadsheet

let's assume the following scenario, which I encounter on a regular basis:

I open a spreadsheet which someone else has created, it is a large workbook with many sheets, each with many columns and hundreds of lines.

I receive a warning pop-up that the sheet is linked to another spreadsheet.

I go to the 'edit ' pull-down menue to see what the linked sheet is called and may or may not know the owner of that external sheet, who may or may not be in my company or in this country.

I need to assess whether I can sever the link or need to update the link etc. or whether any of these options would wreak havoc.

I therefore need to know which cells in the several thousands are linked to this external spreadsheet.

How do I find this out???

Thanks!

RE: finding cells that are linked to another spreadsheet

Hi Andrea. First of all the external worksheet will be referenced in the cell by square brackets. Got to Find and then Options, make sure "In formulas" is selected and in the Find What text box type "[external_worksheet_name", without "]" or anything else. Now if you click on find, it will simply cycle through your thousand or so references to that worksheet, in which case go to the replace tab, leave the replace with text field empty, but choose a format such as a red fill. Then click on Replace All. Every cell with that link in it will now be coloured red and you can sever the link as you see fit!


Hope this helps,

Anthony

RE: finding cells that are linked to another spreadsheet

I have tested this and yes, it does work!
Thanks - that is of great help to me.

Excel tip:

Use shortcut keys to select rows or columns

Most users use the mouse to select rows or columns. It may be more convenient to use keyboard shortcuts to do that.

The shortcut key combination to select an entire row is Shift+Spacebar.

The shortcut key combination to select an entire column is Ctrl+Spacebar.

These are pretty easy to remember as the spacebar looks like a long row (or column if you're looking at it sideways). Remember that Ctrl, beginning with C, selects columns and Shift, by a process of elimination, the rows.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.