Register
Log In
Unanswered
Home
Power Query
Power Pivot
Power BI
July 11, 2017
Offline
Can a formula be written that states the word "WINNER" when all cells in a row have changed to red via conditional formatting
See Answer
Dashboards
Power BI
Power Query
April 21, 2015
Offline
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?
November 8, 2013
Offline
Hi Paul,
You cannot identify the color of a Condtionally formatted cell, not even with vba. But you can evaluate the same formula used in that conditional format.
Iif you can upload a sample file with your rule, we'll help you.
Power Query
Power Pivot
Power BI
July 11, 2017
Offline
Hi Catalin,
In the attached row 17 is the WINNER
Thanks
Paul
July 16, 2010
Offline
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
Power Query
Power Pivot
Power BI
July 11, 2017
Offline
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
Dashboards
June 25, 2016
Offline
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
Most Users Ever Online: 57
Currently Online: clark shao
14 Guest(s)
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 651
Frans Visser: 210
David_Ng: 96
mey tithveasna: 71
A.Maurizio: 60
rathanak: 58
yhooithin05: 54
Anders Sehlstedt: 47
julian: 46
PaulFogel: 37
Newest Members:
Md. Rahman
Lilian Vo
Jane Kumwenda
David Miller
Efe Onwochei
LynAnn Williams
Brandy Kriger
purna chandar
E K
velissa barr
Forum Stats:
Groups: 2
Forums: 18
Topics: 935
Posts: 4405
Member Stats:
Guest Posters: 1
Members: 42350
Moderators: 1
Admins: 3
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea
Moderators: Genevieve Tupas
