0

I have a list of players in my league and next to each name is a checkbox to show if they are active or not. I set up a formula that checks each of the 12 weeks of game play to see if a player participated. If they did participate in any of the 12 weeks, I want the checkbox to be ticked so that their stats get displayed on the stats tab. I'm getting a lot of this and it's displaying #N/A:

ErrorDid not find value 'prduke11' in MATCH evaluation. Invalid: This cell's contents violate its validation rule

When player is active, the checkbox is ticked like it should be. My formula is below (I'm using a nested IF formula but have tried writing it with IFS and got similar results) ($B18 is the player in the list and the formula repeats only changing the Week tab [ex: Week1!, Week2!, etc] it is searching:

=IF(MATCH($B18,Week1!$B$10:$B,0),TRUE,IF(MATCH($B18,Week2!$B$10:$B,0),TRUE,IF(MATCH($B18,Week3!$B$10:$B,0),TRUE,IF(MATCH($B18,Week4!$B$10:$B,0),TRUE,IF(MATCH($B18,Week5!$B$10:$B,0),TRUE,IF(MATCH($B18,Week6!$B$10:$B,0),TRUE,IF(MATCH($B18,Week7!$B$10:$B,0),TRUE,IF(MATCH($B18,Week8!$B$10:$B,0),TRUE,IF(MATCH($B18,Week9!$B$10:$B,0),TRUE,IF(MATCH($B18,Week10!$B$10:$B,0),TRUE,IF(MATCH($B18,Week11!$B$10:$B,0),TRUE,IF(MATCH($B18,Week12!$B$10:$B,0),TRUE,FALSE))))))))))))
  • Welcome. Please share a link to the sheet. – marikamitsos Oct 10 at 3:33
  • @pjmg please don't add breaklines to spreadsheet formulas on questions if that was not the intention of the OP because the low formula readability usually is part of the underlying problem. Instead consider to point the OP to a resource about how to write good formulas or about code smells in spreadsheets, or even better, answer the question and include a brief explanation about how breaklines are added to formulas and how they help. – Rubén Oct 10 at 12:35
0

You can use something along these lines:

=((countif($b18,Week1!$b$10:$b) + countif($b18,Week2!$b$10:$b) + ... ) = 12)

Good luck!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.