July 11, 2017
Can a formula be written that states the word "WINNER" when all cells in a row have changed to red via conditional formatting
VIP
April 21, 2015
As far as I know you can't with the normal Excel functions.
Depending on how the different cells became red you can think of making a normal or nested if function with the same parts in it as were given to make them red (highest of a series or lowest etc.). So if you have an example which didn't work, we can help you with that I think.
Another way is something I red about making your own function in VBA. It is possible to determine which cells are red and so you can make a calculation as well. I'm not sure if I can translate the function I red to English in a proper way, but maybe somebody else here knows already more about this?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
July 16, 2010
Hi Paul,
To clarify, columns J:S contain the winning lottery numbers and columns B:G contain the numbers selected by each person. For someone to win they would need all of their numbers to be present in any single week in columns J:S, not anywhere spread across columns J:S?
This is how the lottery normally works, but I'm not sure if that's what you're doing here. I ask because your Conditional Formatting turns a cell in columns B:G red if the number is present in any of the weeks in columns J:S.
Mynda
July 11, 2017
Hi Mynda,
Each person selects their own 6 numbers and these are put in columns B:G against their name.
Then each week the 6 numbers are entered in the box starting in column J, each week numbers are entered in in columns J:S unti the first person has all of their numbers highlighted, they then win and the process is started again
Regards
Paul
VIP
Trusted Members
June 25, 2016
Hi Paul
One way is to use a COUNTIF to check each column from B:G to determine if the number is found in columns J:S.
If it is found, then it is given a value of 1 and so on. If the total value is 6, then we have a winner.
In your example there are actually 2 winners, row 17 and 61.
Refer to the attachment.
Hope this helps.
Sunny
Answers Post
1 Guest(s)