Last Man Standing Template
Hi. I am trying to create a Last Man Standing template on Excel. The fall down for me is trying to set up the drop down list so that you can only choose a team once during the competition.
I managed to borrow a template that worked it out, but only allows 50 participants. I need at least 100. Can anyone help please?
Thanks
if you give that template here in attachment, then there 'll be no problem to extend it to 100 (or more)
Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
That would be amazing! Thanks and here you go.
where do i find the "list of 100" and where do you make the "list of last man standing" ?
Oh sorry, my bad.
Column D in the Set up tab is where I will input the names of the participants / players. This automatically populates the Pick sheet.
The drop down validation is in the Pick tab. And the formulas and table names start in line 64. You will see there are 50 defined tables. All titled "pick" I cant seem to add any more rows or table names to extend it from 50 to 100.
Hope that makes sense.
Administrative Note:
Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I added a new sheet because i didn't know i could insert a column in "SetUp".
Now you can add as many teams as necessary (<1.048.000) in column A.
Afterwards you can choice a non-selected team in column E.
If you deleted a team, that is already selected in column E, in column A, then that team still stays in column E.
Thanks so much for the work you have put in to this. Can you tell me how I increase the amount of players without corrupting the data?
As i described in that blue form on the right, select A26 (now the first free cell in column A, just below "Paris" and you add there a name. You'll notice that the table expands with a row and the formules in B26:C26 are added without interference.
To select an additional team, a little bit more difficult, select the last team, now E22 "Brussels" and hit the TAB-key, the table expands with a row and now enter manually the new teamname or select the team with the drowdown. After done that, go to the sheet "SetUp" and the new team is now shown as last team in the A-column and consequently also in G, I, K, ....
Later, as you'll need more then 33 rows in "SetUp", you can copy that row and paste it as far as you need.
Last edited by bsalv; 09-07-2022 at 11:14 AM.
I think we may be confusing each other here lol. I dont need to add any new teams, I have added all 20 of the teams required. It's players that I am looking to add.
You will see in the "set up tab" column D a list of players names. There are 50 that populate the "pick" sheet.
In the "Pick" sheet the 50 players names can only select a team once. But if I try to add to the 50 listed the tables stop working.
IMHO, every team below "Westham United" row 22 in Setup isn't in the vlookup-formulas of the columns D, F, H, J, ... of sheet "Pick"Then, just try out as a new version and after removing the protection of both sheetsPHP Code:=IFERROR((VLOOKUP(C26,Setup!$G$4:$AR$[COLOR="#FF0000"][SIZE=3]22[/SIZE][/COLOR],D$52,FALSE)),"")
In Setup
* select the whole row 53 (the row with "player50" in Setup) and insert (click right with the mouse and select insert) 5 empty rows in between player49 and player50.
* select C51:E52 (=players48&49) and drag down until the actual player 50.
* now you should have players 48 until 55 in that range
In PickUp
IMPORTANT : do not touch the (apparently) empty line below the player50, because it contains important numbers !!!
* select the whole row 51 (the row with the former "player50", but now 55 because of previous action) and insert (click right with the mouse and select insert) 5 empty rows in between player49 and player55 (former 50).
* select A49:AR50 (=players48&49) and drag down until the actual player 55 (former 50).
* now you should have players 48 until 55 in that range
* Reinstall the protection on both sheets
Save this new sheet and try out, i gues it'll work.
This is my version of 55 players.
If this works, you can repeat previous actions and add 45 lines instead of 5 to achieve your 100 players
Thank you for this. I tried but it didn't work. Still the same as before in that every player after 50 can select the same team more than once on the drop down. I will keep plugging away. Thanks for your help. I appreciate it.
In addition to what bsalv has done I:
1. Added Pick51 in the name manager with a refers to of: =OFFSET(Pick!$DA$69:$DA$98,0,0,Pick!$DA$68,1)
2. Changed the data validation in cell C52 on the Pick sheet so that the source is: =Pick51
3. Selected cells C52:D52 and then dragged the fill handle over to cell AP52
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
SETUP
> Go to Setup worksheet
> Type team names (it is already set to 20 teams)
SETUP PLAYER and PICK TEAMS
> Go to Player Pick Board worksheet
> Type player name (10 players in the Free version and there are up to 100 players in paid version)
> Start selecting your player?s picks
> Players can pick their teams in every round using a dropdown list
> Do not copy and paste between rows. It will break the dropdown list formula for respective players
PUT ACTUAL RESULTS
> Go to Fixtures worksheet
> Type actual results in fixtures box score fields. Winning teams will be revealed automatically.
> Set wild card by selecting Postponed or Pass in the Status column. It will allow all players to skip a particular match.
TRACK PLAYER?S PREDICTION
> Go to Player Leaderboard worksheet
> See your player standing race in this worksheet. Correct predictions will have green colors. Eliminated players will have a light grey color with incorrect team prediction in its cell at the end of their pick rows.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks