SOLVED

conditional formatting based on formula description

Copper Contributor

Hi there, 

 

I am trying to arrange conditional formatting based on the text of a formula in a cell. The base formule is:

 

=SEARCH("'[";FORMULATEXT(C22);2) / (=VIND.SPEC("'[";FORMULETEKST(C22);2))

So when a formula in cell c22 has the text [' in it, than i want it (conditionaly) formatted.

 

With an if construction as: 

=IFERROR(SEARCH("'[";FORMULATEXT(C22);2)=2;1) /

=ALS.FOUT(VIND.SPEC("'[";FORMULETEKST(C22);2)=2;1)

 

I thought to succeed but i can get it working in condional formatting

 

Basically i want to see (by the formating of the cell) if the value in the cell is a reference to another excel or not.

 

How do i get this formule in a conditional formating format? Or is there a better way to solve this problem?

 

Regards,

DM

 

 

3 Replies
best response confirmed by duco gm mansvelder (Copper Contributor)
Solution

Hi Duco,

 

That could be

=ISNUMBER(SEARCH("[",FORMULATEXT(C22)))

for the English locale, you may convert to your locale here https://en.excel-translator.de/translator/

 

Hi Sergei, Thanks. It works. By the way, i want to copy this conditional formatting formula to all the cells in te spreadsheet. Is there probably a better way to arrange this?

Duco, you are welcome. Don't copy, just apply to your entire sheet. If that's actually full sheet, not the range, apply the rule to

=$1:$1048576

and change in formula the cell reference on A1.

 

1 best response

Accepted Solutions
best response confirmed by duco gm mansvelder (Copper Contributor)
Solution

Hi Duco,

 

That could be

=ISNUMBER(SEARCH("[",FORMULATEXT(C22)))

for the English locale, you may convert to your locale here https://en.excel-translator.de/translator/

 

View solution in original post