Forum Discussion
Beaverbear
Jan 27, 2019Copper Contributor
Conditonal formatting - filter issue
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...
Feb 04, 2019
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.
SergeiBaklan
Feb 04, 2019MVP
Hi Ingeborg,
As cosmetic comment, ISNUMBER could be missed, just
=MATCH($A2,Names!$A:$A,0)
is enough for the rule formula
- Feb 04, 2019
SergeiBaklan 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.
- SergeiBaklanFeb 04, 2019MVP
Oh, IngeborgHawighorst , I know what you know. That's only to provoke a bit people who read to ask themselves "Why so?"