• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HELP Super Bowl Pool

C

cpadilla0024

Member
Hi,

I am trying to create an automated pool template for the Super Bowl this year. I am hitting a roadblock with getting the "neighbors" to auto-populate. "Neighbor" is when someone's square is touching the top, bottom, right, or left of the "direct" square. However, depending on what score hits, someone may have 2, 3, or 4 neighbors. I am trying to get that to fill in the purple shaded section where Neighbors are labelled on the "PAYOUTS" tab. On top of that, I have another purple section under the "Player" column where I want unduplicated names from $C$4:$F$8 to appear in a list and I want blanks to be taken out (blanks are very possible if direct winner has less than 4 neighbors). I have formulas in the two columns to the right for amount owed and pay method.

I put some sample scores in the attached workbook. The direct winner fill is solved. I am just really stuck on finishing this. Ultimately, it would be great to just enter in the scores and have winners and amounts calculate on their own. Thanks so much for any help that can happen.
 

Attachments

  • 2020 Super Bowl Squares.xlsm
    29.8 KB · Views: 4
Looks good! This is awesome!

Would you recommend a button with code to list the unique "neighbors" starting in C12? I was having a hard time ignoring blanks and removing duplicates at the same time.

I attached an updated version. I did some cleanup on row 2 because it was not calculating correctly with the new formulas. Still at a brick wall with the unique list without duplicates and blanks. Preferably without deleting rows because I have formulas next to it.

Thanks for all of your help!
 

Attachments

  • Book2 v2.xlsm
    30.6 KB · Views: 1
Hi,

C4:F8 is what I am aiming for. I tried out the array part and downloaded the example. It is not working though. I even did Ctrl+Shift+Enter. I was thinking that adding VBA may be a better route with a button on the side. I got part of the way it but had issues with blanks still coming up. I copied and pasted each section and did remove duplicates but I am not sure how to exclude the blanks. Ctrl+Shift+Down from row 4 still selects all of it.
 
Thanks!

I will do some testing on it but it looks pretty good with the eye test. Thanks for helping me save time and reduce errors!
 
Hi Narayank,

It is working great. Thanks for all your help! Made my life a lot easier and I will be learning the tools the you used in the parts where I was stuck (array formulas and what you referenced on the neighbors lookup).
 
You must log in or register to reply here.
Back
Top