conditional format where formula uses a defined name

[tom.wolf]

I've researched through the forums and haven't found a specific answer, so my apologies in advance if I just didn't look hard enough.

I want to highlight cells that contain formulas that reference a specific defined name. The purpose is to show where that define is being used, and where it's not being used and perhaps should be. I've seen many uses of GET.CELL, but I can't make it search for the defined name, e.g. "numsys", as opposed to the value.

I don't want to highlight the value of "numsys", because that will just highlight all numbers that may be the result of "numsys", or could simply be constants. The key is where is "numsys" is being used. Thanks.

 

[DonkeyOte]

If for sake of demo. we assume the cells you wish to apply the Conditional Rule to are A1:A10 then with the range highlighted (having selected A1 first):

Create following Names:

Name: =_HasFormula
RefersTo: =GET.CELL(48,A1)

Name: =_Formula
RefersTo: =GET.CELL(6,A1)

Then apply following Conditional Format rule

=IF(_HasFormula,SEARCH("numsys",_Formula))
set format accordingly

The use of _HasFormula will ensure constant references are ignored however it should be noted that where the reference is implicit the above would not highlight, eg:

A1: 
numsys

A2: 
=INDIRECT(A1)

neither would be highlighted (A1 is a constant and formula reference in A2 is implicit)

If you want to apply across various sheets then I'd suggest replacing references to A1 in the Defined Names to INDIRECT("RC",FALSE)
This would ensure that you can use the same names across all sheets (use of ! is unreliable I'm informed by others far more knowledgeable than I)

Word of warning though - CF is super volatile etc so if you "over use" you may experience slowdown in calculation times of your model.

 

[tom.wolf]

Thanks much, it works like a champ. I would never have gotten the need for requiring both GET.CELLs.
I ended up using =GET.CELL(6,INDIRECT("RC",FALSE)) and =GET.CELL(48,INDIRECT("RC",FALSE)) as my spreadsheet has many sheets.

 

[Estherele2]

Hi,
Do you know how to apply conditional Formatting to color only the cells that contains a Defined Name with a "_".
I have a file that contains many Defined Names with "_" in the begining and i would like to show color in the cells that contains them.

Thanks