Aug 13 2022 07:59 AM - edited Aug 13 2022 07:59 AM
NOTE: I’m a novice and this wonderful spreadsheet has been developed with the help of members from this forum, so thank you all very much!
Using Excel in Office 365
I wish to highlight names the same as the highlighted Subtotal cell.
-----------
I have a Lotto spreadsheet where drawn lotto numbers are entered in F18:L28
Individual Names are in column F32 and their Lotto Numbers in: G32: P81
The person(s) lotto numbers are subtotaled in: Q32 to Q81
=SUMPRODUCT(--(COUNTIF($G$18:$M$28,$G32:$P32)>0))
I’ve also applied Conditional formatting to highlight the subtotals to quickly identify those with the most drawn numbers and to ensure duplicate lotto numbers are not counted and grey-out and strike through drawn numbers:
Applies to: =$F$32:$O$81
Formula: =COUNTIF($F$18:$L$28,F32)>=1
Oops, Person 8 should be highlighted vice Person 7.
Again, this currently works well, but I would also like to highlight the person(s) name, the same colour as the subtotal field.
Sorry for any confusion in my description, however, I look forward to some guidance.
Cheers,
Aug 13 2022 08:58 AM
Hi @DBrydon
Not sure this will work because you don't disclose the Conditional Formatting rule that Applies to Q32:Q81 to highligh Subtotals in green
Edit that Conditional Formatting rule. It currently Applies to: $Q$32:$Q$81
Add ,$F$32:$F$81 at the end:
Aug 13 2022 09:29 AM
@L z. Sorry for not providing all the necessary info. I tried to apply your suggestion (hopefully I got that correct :-)), however, no changes occurred.
I've tried to capture the Conditional Formatting screen for your review.
Aug 13 2022 10:56 AM
Solution
- No reason to change the 1st rule (filling grey) as it doesn't apply to your Subtotal col. Keep it is was
- 2nd rule (filling red) can be adapted to Apply to F32:F81
- 3rd rule (Graded Color Scale), no way. Such rule apply to a single column only and that column must contain numeric values. The only way to go would be to replace this rule by n rules, each matching a number/value (or range of numbers) and filling the corresponding cell(s) with a different color
Aug 13 2022 05:22 PM
Aug 13 2022 10:56 AM
Solution
- No reason to change the 1st rule (filling grey) as it doesn't apply to your Subtotal col. Keep it is was
- 2nd rule (filling red) can be adapted to Apply to F32:F81
- 3rd rule (Graded Color Scale), no way. Such rule apply to a single column only and that column must contain numeric values. The only way to go would be to replace this rule by n rules, each matching a number/value (or range of numbers) and filling the corresponding cell(s) with a different color