conditional format where formula uses a defined name

tom.wolf

New Member
Joined
Jan 28, 2011
Messages
2
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,118
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:

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

Name: =_Formula
RefersTo: =GET.CELL(6,A1)
Then apply following Conditional Format rule

Code:
=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:

Code:
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

New Member
Joined
Jan 28, 2011
Messages
2
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

New Member
Joined
Nov 24, 2016
Messages
2
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 :)
 

Forum statistics

Threads
1,082,392
Messages
5,365,165
Members
400,827
Latest member
Mitzescu

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top