Conditional Formatting

[marcr]

I am trying to find out what cells in a column are picked up in a formula elsewhere (in a different tab) and what wells in the column are not. Is there a way to use conditional formatting to color code cells that are picked up by a formula and what cells are not?

Thank you.

 

[DRSteele]

You can use CondFrmt to determine whether a cell contains a formula that refers to another worksheet. This can be achieved by using function SEARCH to look for an exclamation mark (!) in the cell's formula. First, though, we have to use function FORMULATEXT to convert the formula to text.

DO: Select the column of interest (B in my case). Click on Conditional Formatting | New Rule | Use a formula... and paste this exactly into the Format values where... box: =IFERROR(SEARCH("!",FORMULATEXT(B1)),FALSE) and then click on Format, where you can choose a fill-colour that you like. Click on Ok all the way out.

 

[SpillerBD]

You can also use the Formula Auditing Tools. TRACE DEPENDENTS will point to cells on the worksheet using the cell. An icon indicates if the cell is used in another sheet in the Workbook.