Complex conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-944167%22%20slang%3D%22en-US%22%3EComplex%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-944167%22%20slang%3D%22en-US%22%3EHi%2C%20I'm%20looking%20for%20some%20help%20on%20a%20complex%20problem.%20I%20have%20a%20workbook%20with%20multiple%20worksheets.%3CBR%20%2F%3EI%20want%20to%20lookup%20a%20cell%2C%20B1%20on%20worksheet%202%20in%20worksheet%203%2C%20if%20found%20I%20want%20to%20look%20at%20the%20value%20in%20the%20cell%20immediately%20to%20the%20right%20and%20if%20value%20of%20that%20cell%3D1%2C%20then%20format%20background%20colour%20of%20cell%20B1%20on%20worksheet%202%20to%20red.%20If%20the%20value%3D2%20then%20format%20blue%20and%20if%20the%20value%3D3%20then%20format%20B1%20worksheet%202%20orange.%3CBR%20%2F%3EThanks%20in%20advance%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-944167%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-957526%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-957526%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F432688%22%20target%3D%22_blank%22%3E%40Dunk130971%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECan%20you%20clarify%20what%20you%20mean%20by%20%22if%20found%22%20in%20this%20sentence%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3EI%20want%20to%20lookup%20a%20cell%2C%20B1%20on%20worksheet%202%20in%20worksheet%203%2C%20%3CSTRONG%3Eif%20found%3C%2FSTRONG%3E%20I%20want%20to%20look%20at%20the...%3C%2FEM%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EThanks%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-957577%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-957577%22%20slang%3D%22en-US%22%3EHi%20Wyn%3CBR%20%2F%3EIt%20should%20probably%20say%20%22when%20found%22%3CBR%20%2F%3EThanks%3CBR%20%2F%3EDuncan%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-957674%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-957674%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F432688%22%20target%3D%22_blank%22%3E%40Dunk130971%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20lookup%20Worksheet2!B1%20at%20any%20place%20of%20the%20Worksheet3%20or%20in%20some%20specific%20range%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-957686%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-957686%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F432688%22%20target%3D%22_blank%22%3E%40Dunk130971%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20would%20be%20the%20famous%202D%20...%3CSTRONG%3EIFS%3C%2FSTRONG%3E%20trick%20%E2%80%A6%26nbsp%3B%20OK%2C%20so%20I%20exaggerate%2C%20the%20almost%20unheard%20of%20use%20of%20%3CSTRONG%3EMAXIFS%3C%2FSTRONG%3E.%26nbsp%3B%20The%20...%3CSTRONG%3EIFS%3C%2FSTRONG%3E%20functions%20will%20search%20a%202D%20range%20for%20a%20value%20(or%20an%20array%20of%20values)%20and%20return%20the%20contents%20from%20the%20matching%20positions%20in%20a%20further%20similarly-dimensioned%20range.%3C%2FP%3E%3CP%3EBy%20overlapping%20the%20ranges%20(I%20have%20defined%20them%20to%20be%20%3CSTRONG%3E'landingArea'%3C%2FSTRONG%3E%20and%20%3CSTRONG%3E'offsetArea'%3C%2FSTRONG%3E)%20with%20a%20single%20cell%20offset%2C%20the%20formula%20will%20return%20the%20contents%20of%20the%20cell%20immediately%20to%20the%20right%20of%20any%20matched%20cell.%3C%2FP%3E%3CP%3EUnfortunately%2C%20conditional%20formatting%20is%20a%20piece%20of%20stone-age%20junk%20in%20that%20it%20will%20not%20use%20an%20array%20as%20the%20criteria%20to%20format%20a%20range%2C%20never%20mind%20to%20determine%20the%20extent%20of%20the%20CF%20range.%26nbsp%3B%20Hence%20I%20have%20used%20%3CSTRONG%3EINDEX%3C%2FSTRONG%3E%20to%20format%20each%20cell%20individually%20by%20relative%20referencing%20using%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20(format%20%3D%201)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CEM%3Eetc%3C%2FEM%3E.%20where%20%3CSTRONG%3E'format'%3C%2FSTRONG%3E%20is%20given%20by%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20MAXIFS(%20offsetArea%2C%20landingArea%2C%20INDEX(input%2C%20k)%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-958133%22%20slang%3D%22en-US%22%3ERe%3A%20Complex%20conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-958133%22%20slang%3D%22en-US%22%3ESorry%20Duncan%2C%20still%20not%20sure%2C%20what%20are%20you%20trying%20to%20find%20here%3F%3C%2FLINGO-BODY%3E
New Contributor
Hi, I'm looking for some help on a complex problem. I have a workbook with multiple worksheets.
I want to lookup a cell, B1 on worksheet 2 in worksheet 3, if found I want to look at the value in the cell immediately to the right and if value of that cell=1, then format background colour of cell B1 on worksheet 2 to red. If the value=2 then format blue and if the value=3 then format B1 worksheet 2 orange.
Thanks in advance
5 Replies
Highlighted

@Dunk130971 

 

Can you clarify what you mean by "if found" in this sentence

 

I want to lookup a cell, B1 on worksheet 2 in worksheet 3, if found I want to look at the...

 

Thanks

 

 

Highlighted
Hi Wyn
It should probably say "when found"
Thanks
Duncan
Highlighted

@Dunk130971 

And lookup Worksheet2!B1 at any place of the Worksheet3 or in some specific range?

Highlighted

@Dunk130971 

That would be the famous 2D ...IFS trick …  OK, so I exaggerate, the almost unheard of use of MAXIFS.  The ...IFS functions will search a 2D range for a value (or an array of values) and return the contents from the matching positions in a further similarly-dimensioned range.

By overlapping the ranges (I have defined them to be 'landingArea' and 'offsetArea') with a single cell offset, the formula will return the contents of the cell immediately to the right of any matched cell.

Unfortunately, conditional formatting is a piece of stone-age junk in that it will not use an array as the criteria to format a range, never mind to determine the extent of the CF range.  Hence I have used INDEX to format each cell individually by relative referencing using

= (format = 1)

etc. where 'format' is given by

= MAXIFS( offsetArea, landingArea, INDEX(input, k) )

Highlighted
Sorry Duncan, still not sure, what are you trying to find here?