Home

Conditonal formatting - filter issue

%3CLINGO-SUB%20id%3D%22lingo-sub-326332%22%20slang%3D%22en-US%22%3EConditonal%20formatting%20-%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326332%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20adding%20condional%20formatting%20to%20my%20Excel%20Document.%3CBR%20%2F%3Ein%20one%20tab%20I%20have%20a%20colom%20with%20invoice%20due%20dates%2C%20and%20in%20another%20tab%20I%20keep%20track%20of%20the%20payments.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%20when%20the%20payment%20%3D%200%2C00%2C%20the%20referring%20invoice%20due%20date%20gets%20a%20green%20colour.%3CBR%20%2F%3EI%20found%20how%20to%20add%20formula%20with%20conditional%20formatting%2C%20and%20everything%20worked%20fine.%3CBR%20%2F%3EHowever%2C%20when%20i%20did%20some%20filtering%20on%20the%20sheet%20with%20the%20payments%2C%20the%20conditional%20formatting%20wasn't%20up%20to%20date%20anymore%2C%20because%20they%20weren't%20referring%20to%20the%20cells%20that%20I%20pointed%20them%20to.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20question%20is%3A%20when%20you%20do%20conditional%20formatting%20with%20a%20formula%20(if%20cell%3D0%2C00%20then%20this%20cell%20becomes%20green)%2C%20is%20there%20a%20way%20that%20the%20cell%20linked%20too%20in%20your%20formula%2C%20will%20chance%20to%20whichever%20place%20it%20is%20now%20after%20filtering%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-326332%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-330505%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20formatting%20-%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330505%22%20slang%3D%22en-US%22%3E%3CP%3EOh%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%20%2C%20I%20know%20what%20you%20know.%20That's%20only%20to%20provoke%20a%20bit%20people%20who%20read%20to%20ask%20themselves%20%22Why%20so%3F%22%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-330496%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20formatting%20-%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20know%26nbsp%3B%3CIMG%20id%3D%22smileyhappy%22%20class%3D%22emoticon%20emoticon-smileyhappy%22%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Fi%2Fsmilies%2F16x16_smiley-happy.png%22%20alt%3D%22Smiley%20Happy%22%20title%3D%22Smiley%20Happy%22%20%2F%3E%20-%20I%20just%20wanted%20the%20logical%20progression%20from%20worksheet%20formula%20to%20all-in-one%20without%20making%20it%20too%20complex.%20Just%20using%20the%20same%20formula%20seemed%20more%20straightforward.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-330430%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20formatting%20-%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330430%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ingeborg%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20cosmetic%20comment%2C%20ISNUMBER%20could%20be%20missed%2C%20just%3C%2FP%3E%0A%3CPRE%3E%3DMATCH(%24A2%2CNames!%24A%3A%24A%2C0)%3C%2FPRE%3E%0A%3CP%3Eis%20enough%20for%20the%20rule%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-330420%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20formatting%20-%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330420%22%20slang%3D%22en-US%22%3E%3CP%3EThere%20are%20several%20ways%20you%20can%20do%20this.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20is%20to%20create%20a%20helper%20column%20in%20the%20%22tabel%22%20sheet%2C%20using%20the%20formula%26nbsp%3B%20(and%20copy%20down)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3Dmatch(A2%2CNames!A%3AA%2C0)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20a%20match%20for%20the%20name%20is%20found%2C%20the%20formula%20will%20return%20a%20number.%20You%20can%20now%20use%20conditional%20formatting%20to%20look%20at%20this%20helper%20column.%20Select%20A2%20to%20A7%20and%20create%20a%20rule%20that%20uses%20a%20formula%20and%20let%20the%20formula%20be%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3Disnumber(%24C2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20select%20a%20format.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20option%20is%20to%20do%20the%20match%20in%20the%20conditional%20format%20formula.%20Select%20A2%20to%20A7%20and%20create%20a%20rule%20with%20a%20formula%20that%20goes%20like%20this%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3Disnumber(match(%24a2%2CNames!%24A%3A%24A%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20select%20a%20format.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20should%20avoid%20applying%20the%20conditional%20format%20to%20the%20whole%20column%2C%20because%20that%20can%20slow%20the%20spreadsheet%20down.%20In%20the%20Conditional%20Formatting%20Rules%20Manager%20you%20can%20set%20the%20range%20that%20the%20format%20should%20apply%20to.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20are%20in%20Europe%2C%20your%20system%20may%20use%20the%20semicolon%20instead%20of%20the%20comma%20in%20formulas.%20I'll%20attach%20the%20workbook%20with%20the%20formulas%20in%20place.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%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-330144%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20formatting%20-%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-330144%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Ingeborg%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reply.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20added%20an%20exeple%20Excel.%3CBR%20%2F%3EIn%20the%20tab%20%22tabel%22%20I%20want%20the%20names%20to%20be%20highlighted%20in%20red%20if%20they%20are%20also%20in%20the%20colomn%20%22names%22%20in%20the%20tab%20%22names%22.%3CBR%20%2F%3E%3CBR%20%2F%3EAnd%20is%20there%20an%20ease%20way%20to%20copy%2Fpaste%20this%20condittional%20formatting%20to%20the%20entire%20colomn%20in%20tab%20%22tabel%22%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-326371%22%20slang%3D%22en-US%22%3ERe%3A%20Conditonal%20formatting%20-%20filter%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-326371%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eif%20you%20use%20formulas%20for%20conditional%20formatting%20rules%2C%20the%20devil%20is%20in%20the%20detail%2C%20especially%20when%20you%20use%20relative%20references%20without%20the%20%24%20sign.%20It's%20not%20entirely%20clear%20what%20you%20want%20to%20achieve%2C%20so%20please%20post%20a%20sample%20file%20with%20a%20(small!)%20data%20sample%20and%20explain%20in%20context.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Beaverbear
New Contributor

Hi,

 

I have been adding condional formatting to my Excel Document.
in one tab I have a colom with invoice due dates, and in another tab I keep track of the payments.

So when the payment = 0,00, the referring invoice due date gets a green colour.
I found how to add formula with conditional formatting, and everything worked fine.
However, when i did some filtering on the sheet with the payments, the conditional formatting wasn't up to date anymore, because they weren't referring to the cells that I pointed them to.

 

The question is: when you do conditional formatting with a formula (if cell=0,00 then this cell becomes green), is there a way that the cell linked too in your formula, will chance to whichever place it is now after filtering?

 

Thanks! 

6 Replies

Hello,

 

if you use formulas for conditional formatting rules, the devil is in the detail, especially when you use relative references without the $ sign. It's not entirely clear what you want to achieve, so please post a sample file with a (small!) data sample and explain in context.

 

Hi Ingeborg,

 

Thank you for your reply.

I have added an exeple Excel.
In the tab "tabel" I want the names to be highlighted in red if they are also in the colomn "names" in the tab "names".

And is there an ease way to copy/paste this condittional formatting to the entire colomn in tab "tabel"?

Thanks :)

There are several ways you can do this.

 

One is to create a helper column in the "tabel" sheet, using the formula  (and copy down)

 

=match(A2,Names!A:A,0)

 

If a match for the name is found, the formula will return a number. You can now use conditional formatting to look at this helper column. Select A2 to A7 and create a rule that uses a formula and let the formula be

 

=isnumber($C2)

 

Then select a format.

 

Another option is to do the match in the conditional format formula. Select A2 to A7 and create a rule with a formula that goes like this

 

=isnumber(match($a2,Names!$A:$A,0))

 

and select a format.

 

You should avoid applying the conditional format to the whole column, because that can slow the spreadsheet down. In the Conditional Formatting Rules Manager you can set the range that the format should apply to. 

 

If you are in Europe, your system may use the semicolon instead of the comma in formulas. I'll attach the workbook with the formulas in place.

 

 

 

 

 

Hi Ingeborg,

 

As cosmetic comment, ISNUMBER could be missed, just

=MATCH($A2,Names!$A:$A,0)

is enough for the rule formula

@Sergei Baklan I know :smileyhappy: - I just wanted the logical progression from worksheet formula to all-in-one without making it too complex. Just using the same formula seemed more straightforward.

Oh, @Ingeborg Hawighorst , I know what you know. That's only to provoke a bit people who read to ask themselves "Why so?"

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies