Static Random numbers

English
#32

Much accomplished. I do have some questions about layout for you.

First, open the version here and look at 9Team. It’s not even 9 teams yet, but play with the match that has the really heavy border around it by carefully selecting that group of cells. (It will want to show the arrow icon, make sure you miss that and start selecting in one of the corners with the normal selection icon.) Drag it around. See how much fun it is! But, you’ll notice that the next match breaks with an #NA if the moved match dips below the next match. That’s because flow-down arrows expect the arrow tail to be with the bottom player of the previous match, and flow-up matches expect the arrow tail with the top player. Look around at working matches and it’s pretty clear.
Fixing this really makes the code easier, if all arrows flow from, say, the bottom player. But I don’t like that graphically as much.
The big match (final game) has another problem, left/right instead of up/down. I’m curious if you have ideas about how to graphically separate that match so that the top player rests under the word Center up above, but the bottom player is right of Center.
Row spacing??? Do you want the pre-rounds to have one row in common with the first main round (QF1), be above and below them, or ??? Of course, if they don’t share a row then the horizontal spacing has to be increased to 4 empty rows between first main round matches. We seem to have enough horizontal space, but perhaps it would be too toothy? And it would not expand to 16+ teams very well.
Although hidden, there are now two macros to copy a bracket. One copies a live bracket, that then would just need to be assigned a new player list. This is what will be used for development from 9 to 10, etc. The other copies by making an archive. It breaks all player links and just puts the names in place, so you can archive a match to show later without having to occupy a player list, and without the risk of accidently resetting the player list. So, don’t worry about copying sheets with arrows; it’s handled.
Okay–you’ll want to know about how player lookup works. It’s simple. The left of Life 1 or right of Life 2 matches has the connection boxes. That either links to a previous match, or has a number in it. If it links, then the player white space (nearly) beside it will just have =Winner and pick the winner by following the arrow. If it has a number then the white space (nearly) beside it will have =PlayerLookup and get the player from the PlayerList on the PlayerLists sheet that matches the sheet name in question.
To put in the right number is easy. First, set all the links/arrows, hence, all the =Winner lines. Then start at the top and in insertion order put in the numbers 1,2,3,4,… Insertion order is the exact top-to-bottom order you have on the ByeBrackets sheet–but you aren’t entering those numbers, just 1,2,3,4,… The program will translate these into the correct team number, then use that team number (player if single player) to do a lookup on the randomization on the player list.
Next question: How do you want pairing to occur for pairs. The simplest way is to pick the first player identically to single rounds, then add half of the number of players to that for the second player on the team. So with 8 teams 1 and 9 play together, 2 and 10, etc. For randomized teams it doesn’t matter, really. However…
Next next question: Do you foresee wanting a non-random option, where the players compete in the order actually listed on the player list. This option is trivial programmatically, but if we use the 1&9, 2&10 solution, then teams would have to be added that way to the player list, which is very unnatural.
Brackets for Pool Auto Jo9.ods (62.9 KB)

#33

Light at the end of the tunnel. Nearly everything is set as far as brackets, arrows, etc. I still need to add management of the player list when you copy a sheet, which is even required for a sheet archive. Also needing your input for the question of pair play numbering, etc.

Each match, like this:
image
is a separate, copiable unit that you can put anywhere. The only things that you change are the connections to other matches, the location (the table #), and eventually of course the W/L of the upper player. If you use a number for a connection, then that refers to the top-down number listing per the previous post. If it is a formula like =D5 then it refers to the winner of the the match where D5 is just right (for 1st Life) of either the L or the W. (Arrow dot is accounted for either way). That’s it. The final round has to have Center on the first row above it and Final directly above it…a bit of a hack, but it’s solid enough. Reflection for 2nd Life is automatic. The only thing you cannot do is slide a match to the wrong life (across the center column). Other than that, you can have spaghetti if you want, position of the matches doesn’t matter so long as the =D5 (like) references are there for the ‘in’ side and the C’s are there for the ‘out’ side.

#34

Thanks Josh, just writing these emails must take quite a bit of time!!. I know it does with me having to triple check to make sure it’s as right as poss.
I’ll try to enter replies in order,
I do see how that box is movable and the arrows stick with it but don’t really see the purpose of it?. There would be no need to move a bracket unless it’s movable and adapts so it knows its position ?. I do see that this is possibly yr way of doing the bye brackets?.
The brackets must be represented graphically as I show it in ByeBrackets to be properly understood by any viewers/players watching the screens.
I’d love to see your version of a full 10 player bracket so I can see if we’re on the right track.

As I see it the ‘final’ bracket doesn’t need arrows pointing from each winner as the SF1 & 2 brackets obviously lead into the final.
Row spacing - Just as tight as possible as I see it. But in yr 9 Team sheet it is too spaced the 8 team sheet is nice.
The random number set is all that’s required for both singles and pairs
singles - each number is set to a player that number is then placed in a box ie No 1 usually gets a bye and each time the program is used the no1 player should change according to the random set.
Below is a simpler setup that’s different from my first ByeBrackets.
Pairs - each player gets a random number, and as they are random we can just join in sequence for 1st life
for 8 players 1st life, 1&2, 3&4, 5&6, 7&8. 2nd life 1v8, 2v7, 3v6, 4v5
For 9 players 1 gets bye, 2v3, 4v5, 6v7, 8v9, red is offset leading into QF brackets. 2nd life; 3 gets bye, 1v9, 2v8, 4v7, 5v6

10 players - 1&3 get byes in 1st life, 2v4,5v6,7v8,9v10, red are offset brackets. 2nd life; 5&7 have byes, 1v10, 2v9, 3v8, 4v6

I’ve done your ‘Numbers’ sheet in No9 book attached reflecting the above. All the grey (default style) cells are ‘Winner’ from game below (next 2 cells)
Yellow cells are Byes
This method shows a different look at the overall system and I hope that’s the way you meant it?.

We do have one important (Peter O’Neil Memorial) open tournament (pick your own partner) on Kings Birthday Saturday each year where 64 players play in 32 2 man teams, 2 lives. In this the 64 names would need to be entered in pairs order and some need to be added to list. So maybe if you added another green form on playersList to cater for that would be most helpful. Just enter names there and then go to Go32P sheet, bang the setnumbers and we have our brackets set. The order in that would be 1st life 1v2, 3v4, 5v6, 7v8, …,31v32, 2nd life 1v32, 2v31, 3v30, 4v29… 16v17. Obviously no byes. 1st life has 32 games in first round, 16, 8, 4, 2 , final.
I have rushed this a bit so I hope it’s all good
Garry

Brackets for Pool Auto Jo9.ods (54 KB)

#35

Enclosed version is provisionally complete other that sheet duplication and archiving. Hold off on creating other brackets until that is complete, because you’ll want to duplicate sheets for that.

Here are some of the highlights:

  • Graphical form buttons have been replaced with a hyperlink menu. Ctrl-click a menu item to activate it. LO can be set to activate menu items without Ctrl, as well.
  • Multiple players per team is now a first-class feature of the system. Coding is complete for 1 player or 2 players per team, but could easily be extended to 3 or even 4.
  • Teammate assignment is truly random and cannot be schemed by player placement in the player list roster cells.
  • The player list warning flags (red or green) have been reworked to take the number of players per team into account.
  • You can bypass incorrect player lists when loading names. This lets you load 1 through n to act as guides before entering direct-use player names.
  • Non-random, in-order assignment for players or player pairs is now possible.
  • The bracket page is now fully automated once “drawn”, other than table numbers. All player entries on the bracket page are simply =Entry.
  • Player names will shrink to fit as needed.
  • Sensitive locations like the cell location for players-per-team and number-of-teams, or like the lookup row for finding a matching play list for a bracket page are now soft-coded on the _Program sheet. These locations are respected by both the named expressions and the BASIC macros.
  • The macro system now recognizes the “graphical” player list table on the _PlayList sheet and captures play list data based on that table, which can be simply rebuilt from the play list map on that sheet.
  • Lots of deadwood has been cleared in the named expression list – there are now functionally something like 18 fewer named expressions.
  • Basic cell protection has been set to allow for protecting sheets while still allowing entry where needed.
  • Hidable sheets have been renamed with an underscore prefix.
  • An announcement window has been added to the bracket page.

Brackets for Pool Auto Jo16.ods (63.7 KB)

#36

Thanks Josh
Impressive!!!.
Do I understand this as I can set the bracket sheets automatically?. Will it generate a 9 bracket sheet for instance?. I don’t see what the ‘Hyperlinks’ are actually doing maybe it generated the 15 Team_1190 sheet once only, not sure. Don’t get the Early, Late, Full menu choices as nothing changes.
Adding names to playerslist, they are not stored when new names entered and can’t be sorted on the sheet with 2 columns (Data-Sort). They are sorted in the DropDown list thou.
The 15 Team sheets first bracket top left and right has a 4 row gap to next bracket, why?. Is this a problem with the up/down thingy . Better the gap is 1 row as per all other brackets by dropping down 3 rows?.
Also in 15 Team sheet you only have 14 names in brackets, there is no No1 (Alan) who has a bye so should be in first bracket in L column
Could the table number be cleared when W or L entered, either clear number or change font color to green
In playerslist ‘Players per Team’ I get 2 variant results ie; enter 1 = 15 team, enter 1 (1+space) = 1 ???
On yr Bracket sheet it looks nice, perfect spacing but what is ‘PlayerCountPerTeam’ and ‘TeamCount’ for, they don’t seem to have any function or entry point.
After a quick look.
Once again 'IMPRESSIVE":muscle:
Garry

#37

Quick responses:

  • No. The brackets are not automatic, but they can be simply drawing by copying the match units as described above. Each match unit (the screen snippet above) can be freely copied and placed, then set to pull a name from the list or winner, etc.
  • Use Ctrl+click or just click the hyperlinks depending on your settings in LO. See Ctrl or Not.
  • There is a macro for sorting the full playerlist. It also does column balancing. I’ll see about getting that up and running if you are not in a hurry. Otherwise, it’s easy enough to just use one column, but then you’ll be scrolling. Had also considered doing a mark-and-load option, where you select the players you want then load them into the clipboard list??
  • The gap for 15 player bracket reflects the logic of byes. But just select the entire match unit (as noted above) and slide it anywhere you want. Again, the layout is completely flexible.
  • I double checked, and everyone shows up in the brackets. You can put player 1 in a 16th match unit and we can set up a B for Bye or something, if that is what you are looking for. But that wouldn’t reflect your bye-bracket graphics. Or, if I misunderstand those graphics, then just move the bye round out and the match round in?? Explain to me what you are wanting.
  • I can look at hiding table numbers after a match is complete. I think that should just be a conditional formatting, but it might have to be assigned per table, unlike the other CF. That would make it more of a question for how much detail you want to do when setting up the bracket sheets. I’ll think about it.
  • I don’t understand 1 = 15, enter 1 … I can say that the top number is whether or not you are playing singles or pairs per team. The bottom number is the number of teams. Either one is really meant to be set-and-left for a given bracket sheet, but you can move between them for some testing until we protect the sheets.
  • The _Bracket sheet is programmatic. That defines locations for those quantities on the actual sheet. I think I use direct links as it is, but, for example, the _PlayList sheet is also programmatic, and the location of the Assign1, Name1, etc., are vital to the function of the system. Well, they are compiled into a table, but that table is vital. These are text labels. There is no ‘=’. The get read by the PatternMapToTable macro during development cycles.
#38

Hi J
Replies;

  • Automatic was just a hopeful thought!. I do know I can move the brackets.
  • Yes I was using CTRL but I wasn’t seeing any reaction, but on a closer look it is changing but I’ve yet to determine why??.
  • No stick with your 2 columns that’s nicely visible and if you have a sort macro, cool.
  • Yip as above
  • The 15 Team sheet is attached, so you see what I’m looking at, with 7 full brackets on Left =14 players and NO name in 2nd tier top which has to show No1 Alan who has the Bye (15th Player) and the winner of 1st tier Robert/Lee game then plays Alan. That must be done as per my graphical brackets.
  • In playerslist ‘Players per Team’ I get 2 variant results ie; enter 1 = 15 team, enter 1 (1+space) = 1 ??? Try entering 1 there and ‘15 Team’ shows in box (should that be an option?) then enter 1 space that gives a 1 (expected result).
    In your ‘Menu’ I don’t like Load Randomly as this comes back to ‘banging the button to suit the person using it’ possibly. Why is it there as an option?.
    Garry

Brackets for Pool Auto Jo16.ods (68.8 KB)

#39
  • The Show Full Tournament, etc. buttons crop the outer or inner parts of the contest, so the page can be enlarged to show text larger on a big screen TV/monitor for that part of the tournament.
  • On 15Team somehow the 3 for Lee got changed to %. So Lee didn’t show up as the lucky bye player.
  • Random list generation or ordered list generation is just as dependent on one-shot as ever. Still just disabled for testing. The option is there for being able to do random teams or have pre-matched players for the King tournament.
  • I still don’t understand at all what you are talking about. Sorry. Any player or team count information is entered on the bracket page itself.

Some things are calling…I hope a slowed turnaround is okay. Let me know if you get nervous.

#40

Hi Josh
Yes this has become a very stretched out project. I initially thought it would take a week to get it done, its now been 4 weeks. I need to try and see if I can get some versions working from my limited understanding of your complex coding. I’ll try a sheet to see how I go but obviously I’ll need some help from you but I’ll try to limit it as much as possible.
Garry

#41

Everything works on this end. I’ll post the latest version tomorrow if I can. I might want to add some auto renaming of the playlists, etc. But with the version I’ll post you can build any bracket from 4 single players to 32 pairs, and sheets you build would survive versioning. If you want 2nd life for the 32 pair tournament, that will take adding columns…but there’s plenty of time if that’s MLK Day…

For what I understand you need, you’ll just copy the 15Team using the hyperlink menu at the right…duplicate, not archive. Then you’ll add the one missing match on the left and right to get a 16Team sheet. Once that is done, it’s just a matter of duplicating 15Team and deleting unneeded matches for 14Team, etc. On each new sheet, set the win links just with =D11 or whatever, like on 15Team, but as needed. Then when the win links are done, just number down the original (far left/far right) 1,2,3,… That’s it. Save, copy that sheet, then keep moving down by deleting and rewiring as necessary. There really should be no need to touch the code at all, other than at some point to comment out the Goto that allows unlimited player loading. You may need to delete some extra player lists at some point, which you would do by deleting the entire columns for those player lists.

#42

This isn’t perfect, but it should be within range of the request.

Brackets for Pool Auto Jo17.ods (61.4 KB)

#43

Hi J

A quick thought and is probably yr plan anyway?.
Is yr ‘Sheet Name’ list on Program sheet a method of loading any sheet required for a nights game rather than having, my initial way, of 10 or so sheets displayed in sheet list at bottom (Go_8, Go_9, Go_10) and picking the one required. So create a G0_9 sheet (covers 9_Singles & 9_Pairs) save it and it loads on choosing it in ‘Sheet Name’ List.
Is that the way you see it?.
Garry

#44

On a better look the ‘Sheet Name’ list on Program sheet is just a reference as clicking on them does nothing.

#45

Hi Josh
You did say your last send was a working version?.
I’ve set up the 15 Team sheet as I want it (moving brackets into place) but nothing is working.

  • It appears there’s no random number allocation
  • No populating of brackets, they are all blank
  • no1 player must have the bye but no 3 is in second tier bracket, do I just swap 1 & 3 with 1st tier top bracket? (can’t check as no names there)
  • Which PlayerList sheet are we working with _PlayerList or PlayerLists. The latter has the namelist so what’s _PlayerList there for?.
    Are you in the military with all your NATO names in list?.
    Garry
#46

Can you post your version?

#47

Hi
Here it is
G

Brackets for Pool Auto Jo17.ods (64.5 KB)

#48

You don’t have 15Team selected for the player list on the player list page. Just go to PlayerLists and use the drop list to select 15Team.

#49

Hi J
I did originally have 15 selected on PlayerLists but that made no difference so I thought I’d make a 9 player sheet, that’s why the 9 was there.
Have you made it work?
I put 15 back there and 15 back on 15Team sheet but nothing changes. Brackets are still blank.
What am I supposed to do here?.
G

#50

I took your file, went to the PlayerLists sheet, selected 15Team, and everything worked as expected. I’m happy to help, but I don’t know what else to say.

#51

Ok I didn’t have 15Team in there and now they’re all populated. I like it how the only populated sheet is the sheet listed there, that’s cool!!.

So are the players all given a random number?, can I see what number is allocated to each player so I can see the bye is operating correctly. As I see it the numbers relate to the List numbering and are not randomly generated. They need to be. Do we need SetNames box back?.

The 1st life numbers for players are coming up differently ie Foxtrot is 6 in 1st life (as per list and NOT random) but then pops up as 3 and has the bye in 2nd life. How is that happening? It would suggest different numbering systems for each life?.

Are you using the SeatLookup sheet as a positioning tool for the brackets. If yes, No1 always has 1st bye in 1st life and no3 in 2nd life, I don’t see that happening there?. Can u demonstrate how SeatLookup works from your point of view please

With adding new sheets I name them 9Team and 10Team but the SheetName on Program sheet has it as Sheet9 and sheet10 (System Names) and can’t be renamed there. This could be confusing to a user. I now see on reload that the names are correst in Program!, it just doesn’t happen instantly.

How should I go adding a new 9 player sheet;

  • Do I duplicate 15Team sheet and delete Brackets until correct setup achieved and Rename sheet to 9Team (as I did in upload)
  • Or copy brackets and paste into new sheet. This method has no formatting and requires a lot of work
  • Are there other methods
    In PlayerLists sheet adding a name more than the number of players in ‘Entries Listed’ used to go RED but it doesn’t now, you can have 15 there and it’s green

Garry

Brackets for Pool Auto Jo17.ods (69.9 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.