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
Conditional Formatting Formulas
jfh117 in Excel on
11 Replies
Conditional policies in Azure AD vs. Intune
Robert Woods in Microsoft Intune on
14 Replies
Device Compliance
Baljit Aujla in Microsoft Intune on
21 Replies
Condition Access Question
Quinn Wade in Microsoft Intune on
1 Replies