SOLVED
Home

conditional formatting based on formula description

%3CLINGO-SUB%20id%3D%22lingo-sub-288198%22%20slang%3D%22en-US%22%3Econditional%20formatting%20based%20on%20formula%20description%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288198%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20arrange%20conditional%20formatting%20based%20on%20the%20text%20of%20a%20formula%20in%20a%20cell.%20The%20base%20formule%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSEARCH(%22'%5B%22%3BFORMULATEXT(C22)%3B2)%20%2F%20(%3DVIND.SPEC(%22'%5B%22%3BFORMULETEKST(C22)%3B2))%3C%2FP%3E%3CP%3ESo%20when%20a%20formula%20in%20cell%20c22%20has%20the%20text%20%5B'%20in%20it%2C%20than%20i%20want%20it%20(conditionaly)%20formatted.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20an%20if%20construction%20as%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(%3CSPAN%3ESEARCH(%22'%5B%22%3BFORMULATEXT(C22)%3B2)%3D2%3B1)%20%2F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DALS.FOUT(VIND.SPEC(%22'%5B%22%3BFORMULETEKST(C22)%3B2)%3D2%3B1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20thought%20to%20succeed%20but%20i%20can%20get%20it%20working%20in%20condional%20formatting%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%20i%20want%20to%20see%20(by%20the%20formating%20of%20the%20cell)%20if%20the%20value%20in%20the%20cell%20is%20a%20reference%20to%20another%20excel%20or%20not.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20i%20get%20this%20formule%20in%20a%20conditional%20formating%20format%3F%20Or%20is%20there%20a%20better%20way%20to%20solve%20this%20problem%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EDM%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-288198%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EConditional%20Formatting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288212%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20based%20on%20formula%20description%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288212%22%20slang%3D%22en-US%22%3E%3CP%3EDuco%2C%20you%20are%20welcome.%20Don't%20copy%2C%20just%20apply%20to%20your%20entire%20sheet.%20If%20that's%20actually%20full%20sheet%2C%20not%20the%20range%2C%20apply%20the%20rule%20to%3C%2FP%3E%0A%3CPRE%3E%3D%241%3A%241048576%3C%2FPRE%3E%0A%3CP%3Eand%20change%20in%20formula%20the%20cell%20reference%20on%20A1.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288209%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20based%20on%20formula%20description%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288209%22%20slang%3D%22en-US%22%3EHi%20Sergei%2C%20Thanks.%20It%20works.%20By%20the%20way%2C%20i%20want%20to%20copy%20this%20conditional%20formatting%20formula%20to%20all%20the%20cells%20in%20te%20spreadsheet.%20Is%20there%20probably%20a%20better%20way%20to%20arrange%20this%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-288202%22%20slang%3D%22en-US%22%3ERe%3A%20conditional%20formatting%20based%20on%20formula%20description%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-288202%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Duco%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DISNUMBER(SEARCH(%22%5B%22%2CFORMULATEXT(C22)))%3C%2FPRE%3E%0A%3CP%3Efor%20the%20English%20locale%2C%20you%20may%20convert%20to%20your%20locale%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fen.excel-translator.de%2Ftranslator%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fen.excel-translator.de%2Ftranslator%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
duco gm mansvelder
Occasional 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
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.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies