cpadilla0024
New Member
- Joined
- Jan 4, 2021
- Messages
- 8
- Office Version
- 365
- 2019
- Platform
- Windows
Hey everyone,
Just trying to save time if possible when creating pages for my players. I have been trying to work on a button that generates/saves a PDF for each player/group in my pool (Superbowl_PlayerNames.pdf). Groups would be because they are a couple. The table is formatted to highlight their squares based on the names in the highlighted section off to the left (#1). #2 is where my player list is stored and I created a "Save" column to note how the grouping goes. If there are two players with the same number here, they are sharing a sheet. The odd part here for me too is getting the shared names in the appropriate boxes in #1 so the highlights on the actual table happen.
Any help I can get would be really appreciated and a huge timesaver instead of saving one by one.
#1
#2
Just trying to save time if possible when creating pages for my players. I have been trying to work on a button that generates/saves a PDF for each player/group in my pool (Superbowl_PlayerNames.pdf). Groups would be because they are a couple. The table is formatted to highlight their squares based on the names in the highlighted section off to the left (#1). #2 is where my player list is stored and I created a "Save" column to note how the grouping goes. If there are two players with the same number here, they are sharing a sheet. The odd part here for me too is getting the shared names in the appropriate boxes in #1 so the highlights on the actual table happen.
Any help I can get would be really appreciated and a huge timesaver instead of saving one by one.
#1
2021 Superbowl Squares1.xlsm | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | HOME | |||||||||||||||||||||
2 | AWAY | HOME | ||||||||||||||||||||
3 | ||||||||||||||||||||||
4 | Qtr 1 | Quarter 1: | - | |||||||||||||||||||
5 | Qtr 2 | Quarter 2: | - | |||||||||||||||||||
6 | AWAY | Qtr 3 | Quarter 3: | - | ||||||||||||||||||
7 | Final | FINAL: | - | |||||||||||||||||||
8 | Vince | Bernie | Hollie | Angel | Chauncy | Will | Teezy | Joey | Riq | Ryan | ||||||||||||
9 | Chauncy | Stace | Ruben | Thea | Josh | T Bone | Angel | Nate C | Matt | Vince | ||||||||||||
10 | Mike V | Linda Wu | Robbie | Kathy | Winnie | Brian | Hollie | Monica | Will | Joey | ||||||||||||
11 | T Bone | Trav | Nate C | Robbie | Juan | Ruben | Lorenzo | Josh | Chauncy | Teezy | ||||||||||||
12 | Kathy | Brian | Joey | Will | Robbie | Mike V | Matt | Riq | Thea | Josh | ||||||||||||
13 | Juan | Teezy | T Bone | Ryan | Vince | Carlos | Theresa | Bernie | Nate C | Will | ||||||||||||
14 | Riq | Josh | Thea | Henry | Joe | Angel | Carlos | Josh | Ruben | Stace | ||||||||||||
15 | Winnie | Will | Angel | Ruben | Joey | Nate C | Will | Carlos | T Bone | Theresa | ||||||||||||
16 | Trav | Nate C | Will | Trav | Josh | Riq | Linda Wu | Brian | Angel | Vince | ||||||||||||
17 | Vince | Trav | Matt | Lorenzo | Mike V | Trav | Trav | T Bone | Henry | Ryan | ||||||||||||
18 | ||||||||||||||||||||||
19 | Price/Square: | $ 10 | Total Payout/Qtr: | $ 250 | ||||||||||||||||||
20 | ||||||||||||||||||||||
21 | #1 | Payouts are for Quarters 1-3 and Final scores. | Direct with 2 neighbors: | $ 190 | per Qtr | |||||||||||||||||
22 | #2 | New set of numbers per quarter (see above). | Direct with 3 neighbors: | $ 160 | per Qtr | |||||||||||||||||
23 | #3 | Payout structure pays direct and top, bottom, left, and right neighbors. No diagonal neighbors are paid. | Direct with 4 neighbors: | $ 130 | per Qtr | |||||||||||||||||
24 | Neighbors: | $ 30 | each per Qtr | |||||||||||||||||||
Squares |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D1 | D1 | =+IF(T2="","",T2) |
R2 | R2 | =+IF('Player List'!C2="",'Player List'!B2,'Player List'!C2) |
T2 | T2 | =+IF('Player List'!C3="",'Player List'!B3,'Player List'!C3) |
A6 | A6 | =+IF(R2="","",R2) |
H19 | H19 | =+'Player List'!C5 |
M19 | M19 | =+('Player List'!C5*100)/4 |
M21 | M21 | =+$M$19-($M$24*2) |
M22 | M22 | =+$M$19-($M$24*3) |
M23 | M23 | =+$M$19-($M$24*4) |
M24 | M24 | =+'Player List'!C5*3 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
F8:O17 | Expression | =COUNTIFS($T$14:$T$16,F8)>0 | text | NO |
E8:E17 | Cell Value | duplicates | text | NO |
D8:D17 | Cell Value | duplicates | text | NO |
C8:C17 | Cell Value | duplicates | text | NO |
B8:B17 | Cell Value | duplicates | text | NO |
F7:O7 | Cell Value | duplicates | text | NO |
F6:O6 | Cell Value | duplicates | text | NO |
F5:O5 | Cell Value | duplicates | text | NO |
F4:O4 | Cell Value | duplicates | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
T14:T16 | List | =PlayerNames |
F8:O17 | List | =PlayerNames |
#2
2021 Superbowl Squares1.xlsm | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | |||
7 | ENTER | ENTER | ENTER | ENTER | |||||||
8 | Player Name | # of Squares | % of Total Squares | Amount Owed | Amount Paid | Fully Paid? | Pay Method | Square Check | Save | ||
9 | Ryan | 3 | 3% | 30 | 30 | Yes | Venmo | GOOD | 1 | ||
10 | Brian | 3 | 3% | 30 | 30 | Yes | Venmo | GOOD | 2 | ||
11 | Vince | 5 | 5% | 50 | 50 | Yes | PayPal | GOOD | 3 | ||
12 | Carlos | 3 | 3% | 30 | 30 | Yes | Bank | GOOD | 4 | ||
13 | Nate C | 5 | 5% | 50 | 50 | Yes | PayPal | GOOD | 5 | ||
14 | Josh | 6 | 6% | 60 | 60 | Yes | Venmo | GOOD | 6 | ||
15 | Angel | 5 | 5% | 50 | 50 | Yes | Venmo | GOOD | 7 | ||
16 | Will | 7 | 7% | 70 | 70 | Yes | Venmo | GOOD | 7 | ||
17 | Linda Wu | 2 | 2% | 20 | 20 | Yes | PayPal | GOOD | 8 | ||
18 | Ruben | 4 | 4% | 40 | 40 | Yes | Venmo | GOOD | 9 | ||
19 | Lorenzo | 2 | 2% | 20 | 20 | Yes | PayPal | GOOD | 10 | ||
20 | Chauncy | 1 | 1% | 10 | 10 | Yes | PayPal | GOOD | 11 | ||
21 | Thea | 3 | 3% | 30 | 30 | Yes | PayPal | GOOD | 12 | ||
22 | Juan | 2 | 2% | 20 | 20 | Yes | Venmo | GOOD | 13 | ||
23 | Theresa | 2 | 2% | 20 | 20 | Yes | Venmo | GOOD | 13 | ||
24 | Joe | 1 | 1% | 10 | 10 | Yes | Venmo | GOOD | 14 | ||
25 | Monica | 1 | 1% | 10 | 10 | Yes | Venmo | GOOD | 14 | ||
26 | Mike V | 3 | 3% | 30 | 30 | Yes | Venmo | GOOD | 15 | ||
27 | Kathy | 2 | 2% | 20 | 20 | Yes | Bank | GOOD | 16 | ||
28 | T Bone | 5 | 5% | 50 | 50 | Yes | Bank | GOOD | 17 | ||
29 | Joey | 4 | 4% | 40 | 40 | Yes | Venmo | GOOD | 18 | ||
30 | Bernie | 2 | 2% | 20 | 20 | Yes | Venmo | GOOD | 19 | ||
31 | Riq | 4 | 4% | 40 | 40 | Yes | Venmo | GOOD | 20 | ||
32 | Henry | 2 | 2% | 20 | 20 | Yes | Venmo | GOOD | 21 | ||
33 | Hollie | 2 | 2% | 20 | 20 | Yes | Venmo | GOOD | 21 | ||
34 | Winnie | 2 | 2% | 20 | 20 | Yes | Venmo | GOOD | 22 | ||
35 | Matt | 3 | 3% | 30 | 30 | Yes | Venmo | GOOD | 23 | ||
36 | Stace | 2 | 2% | 20 | 20 | Yes | Venmo | GOOD | 24 | ||
37 | Chauncy | 2 | 2% | 20 | 20 | Yes | Bank | GOOD | 11 | ||
38 | Trav | 6 | 6% | 60 | 60 | Yes | Bank | GOOD | 25 | ||
39 | Teezy | 3 | 3% | 30 | 30 | Yes | Venmo | GOOD | 26 | ||
40 | Robbie | 3 | 3% | 30 | 30 | Yes | PayPal | GOOD | 27 | ||
Player List |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D9:D40 | D9 | =+C9/100 |
E9:E40 | E9 | =+C9*$C$5 |
G9:G40 | G9 | =+IF(E9=F9,"Yes","No") |
I9:I40 | I9 | =+IF([@[Player Name]]="","",IF(SUMIFS(['# of Squares],[Player Name],[@[Player Name]])=COUNTIF(PlayersSquare,[@[Player Name]]),"GOOD","BAD")) |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
PlayerNames | =PlayerTable[Player Name] | I9:I40 |
PlayersSquare | =Squares!$F$8:$O$17 | I9:I40 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
I9:I40 | Cell Value | ="BAD" | text | NO |
G9:G40 | Cell Value | <>"Yes" | text | NO |