Conditional format cells linked from another sheet

Question

Hi

Is there a way to color code only those cells in a particular worksheet that are linked from other sheets in that same Excel file?  For example looking at a worksheet,  cells are shaded with a grey background to indicate they are linked to another sheet.  if they are not linked to another sheet, then they are not shaded grey.

thanks!

Craig

Answer 1

Jason is on the right track but for my conditional formatting formula I used

=ISNUMBER(FIND("!",FORMULATEXT(A1)))

In the screenshot two cells (C1 and C3) reference another sheet;

I selected all the cells and used the formula above (note it refers to A1 since this was the first cell in my selection

best wishes

Answer 2

I don't have Excel 2016, but it has the FORMULATEXT function.

Create a conditional format, using the IFERROR, FIND and FORMULATEXT functions together as the rule. Like this:

=IFERROR(FIND("!",FORMULATEXT(A1))>0,FALSE)

And then set the color you want.

I can't test it, but it should work.

Answer 3

thanks it works!

Answer 4

CraigBrody,

re:  identify cells linked to other sheets

Couple of things to try...

Use the Trace Dependents or Trace Precedents icons on the: 

  Ribbon | Formulas (tab) | Formula Auditing (group)

-or-

Do a Search for ! (a single exclamation point) using the 'Find All' button on the search dialog box.

When found, with the dialog still open, use Ctrl + A (keyboard) to select all of the found cells.

Color the cells and close the dialog box.

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)

thanks for the tips...while very helpful to know, i was looking for a conditional formatting solution so if linked formulas to other sheets are entered in a sheet they would turn color automatically while normal formulas dependent on cells within that sheet would not.

thanks

Answer 5

CraigBrody,

re:  identify cells linked to other sheets

Couple of things to try...

Use the Trace Dependents or Trace Precedents icons on the: 

  Ribbon | Formulas (tab) | Formula Auditing (group)

-or-

Do a Search for ! (a single exclamation point) using the 'Find All' button on the search dialog box.

When found, with the dialog still open, use Ctrl + A (keyboard) to select all of the found cells.

Color the cells and close the dialog box.

'---

Jim Cone

Portland, Oregon USA

https://goo.gl/IUQUN2 (Dropbox)

(free & commercial excel add-ins & workbooks)