Thanks
I have quickly made a TEST 8-S sheet for your critique.
Problems with this version;
The Set Names box has lost it’s magic (msgbx) so I can smack the numbers willy nilly
The B col random numbers are not unique
Garry
Brackets for Pool Auto (2).ods (51.1 KB)
Lots of advancement. Look forward to:
However, the 504 error is not totally gone yet.
Notice that the one-shot protection is intentionally disabled for testing.
Deadwood random numbers are just left sitting at the left of the player list right now…they are ignored (I think 
).
Edit: Corrected scripting error
Brackets for Pool Auto Jo4.ods (51.4 KB)
Hi Josh
I’m in awe of your enthusiasm for this project. Your latest installment amazed me with how much time, thought, and effort you;ve put into it.
I have been busy with life down here so I’ve only glanced at it and played with the diff sheets some.
I can see this being a way to enter sheets easier and mainly its extendablility.
Thanks!!!
Garry
Hi Again
I’ve tried making up a go9-S sheet by copy-pasting the 8s sheet onto it and adding a left bracket but all sorts of things go wrong.
Also none of the TDs came across.
The new left bracket numbers are 2-9, the odd numbers always have the byes so 1 is in top second tier place. On 10 player bracket 1 & 3 have byes etc.
9 player setup; 10 player setup; 11 player setup; 12 player setup; 13 player setup;
1 1 1 1 2-13
2-9 2-10 2-11 2-12 4-12
3-8 3 3 3 1
4-7 4-9 4-10 4-11 6-11
5-6 5-8 5 5 3
6-7 6-9 6-10 7-10
7-8 7 5
8-9 8-9
I Hope these ‘setups’ render properly for you to see
Can you see if your system has a way of doing this better/easier?. There are a lot of these BYE sheets.
It’s only the even number sheets over 15 players that are PAIRS sheets (16,18,20,22,24,32)
Look forward to next installment
Garry
Brackets for Go9-S.ods (53.7 KB)
I’ve setup a demo 9 team sheet so you can see if you are okay with the format for an odd number of teams.
So, of course, by team numbers I mean before randomization…I mean what I call the PlayerKey.
(Pairs or singles doesn’t matter, really. If the numbers are automated, sort-of both have to work. Again, in fact, you can just put 1 instead of 2 in the 8Team sheet header for team size and it will convert. Then it will complain when you go to set names, and you have to delete half the players from the player list. But the sheet is the same. At least, that remains the plan.)
Brackets for Pool Auto Jo5.ods (59.1 KB)
Hi Josh
Thanks for getting back to me so quickly.
Not the ideal setup with the extra bracket added to the first line it would get quite ugly as you added brackets down for more players
I’ve quickly done a more graphical example of the traditional setup for 1 player teams in ByeBrackets sheet
If you grasp that then you may be able to transfer it to the number setups I sent in previous email.
If you can get my Go9-S sheet to work your way as that’s how it needs to be
Hope it helps
Garry
Brackets for Pool Auto Jo5.ods (59.1 KB)
Forgot
TD = trace dependants
Okay, I see the bye pattern for the graphical side of things. Yes, tucking the pre-round into the first “normal” round would work for 9, but clearly not for more teams. Thanks for the sketches.
The arrows you add, recall, by clicking on the destination blue spots (the ones that have =E8 or whatever, not the C itself) and press Shift+F9. It takes moments to do a sheet. We might post here about a way to preserve the traces on sheet duplication so you can, for example, save back played brackets. The failure to duplicate seems to be a limit of Calc, but it’s just eye candy, so it’s a later concern.
I do need the confidence, though, of having the completed team number lists. I’ve done the 9 team version as I see it from your graphic. Please do fill out the right side (second life) for 9, and do any others you want on the Numbers sheet. Actually, some of that may just get integrated into the program. Use a W whenever the pre-match carries a winner over to the “normal” first round of play.
I really cannot literally use your sheets for development. I know that sounds harsh, but without much more robust versioning, etc., I would be left second-guessing your changes. I pretty much have to rebuild my last version based on your work. But I definitely can* set up the pre-match system like your sketches if you get me the numbers so I know it’s what you want. (*Unless I can’t! 
No, really, it’s easily enough done one way or the other.)
Thanks!
Brackets for Pool Auto Jo5.ods (44.7 KB)
Sorry Josh I can’t work your number system, I’m not really seeing how to do it that way.
So I’ve continued with my graphical version in ByeBrackets and I’ve added the right side numbers underneath each group. They’re Right to Left
I’ve done 9 to 15 players, any above that are catered for by your 1-2 team number and if 17-19-21-23 we just ask for another player to make up to an even number and run pairs. We do this with 15 players too but occasionally we do play singles when no one makes it up to 16.
I hope u can work with this interpretation. Maybe you can flick this into your number system?
Garry
JoshNumbers.ods (48.1 KB)
Are u still with us Joshua
Garry
Your diagrams are much better than the number lists, even though I may end up boiling them down to that at some point. Thank you.
Adding the pre-rounds should be relatively straightforward, but not simple.
Right now I’m having to circle some wagons in the code. Could jam it through, but to be perfectly honest, some of the value of this project is developing certain techniques for use in other projects. So there is a balance. Time vs. doing-it-right, where doing-it-right has the bigger payoff, well, for me. I promise it isn’t feature bloat. It’s factoring out routines that can be otherwise useful generally. This makes for stronger, more editable code for the future, too, of course.
QUESTION: As I see it, you kind of “cheat” on the bye round diagrams…sometimes a box is a player, sometimes both players. Am I right? Without worrying about borders or lines or anything, can you give me, say, the 10-player look? I’ve put in a sheet with an 8-player version of what I’m looking for. KISS. The sheet is already named, though, 10PlayerSketch. The row spacing is what is important.
The version I’m sending is PROBABL
Brackets for Pool Auto Jo7.ods (64.0 KB)
Y BROKEN. Don’t fear.
Hi Josh
No worries about you getting your code right, I reckoned that’s what you were doing.
Just quickly on the byes etc ‘sometimes a box is a player, sometimes both players’, no I don’t ‘cheat’ here at all. It’s very structured
That’s the BYE system, if only one player in box then that player has a bye (free pass to next level) and the winner of previous box moves into that box.
So in the 10 player right side the winner of 1v6 moves left to play 5. 1v6 means no.1 versus no.6 random number.
To elaborate;
There are set patterns with byes. All odd numbers are used first up to the number of byes required (10 players -1&3 in 1st life, 5&7 in 2nd). In 12 player bracket there are 4 byes in 1st life (1,3,5,7) and 4 byes in 2nd life (9,11,2,4), These numbers should ensure that there are no double ups in games, say 1v6 plays each other in both lives, we try to avoid that. And the bye person/s only has a bye once. Also we try to make it so players should not play each other twice before the SFinal
There are always 4 boxes in third tier from final. So the order backwards is- final, then SF1, QF1, 8 players/teams (4boxes) always.
After 12 players the first tier adds another bracket so 13=5 boxes, 14=6 boxes and 15=7 boxes. That is how we get back to 8 players in third tier from the final.
You’ll notice in ByeBrackets sheet I noted the number of byes (row 2) which sequence up and down from 8 players (no bye) up to 12 (4 byes) and then down to 15 (1 bye).
Hope I’ve read your confusion correctly in last email and this helps?. All that 1st tier and 3 tiers back etc is as plain as day🤔
Garry
Did my last postings help you at all?.
If not, let me know how I can help further.
Garry
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)
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:
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.
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)
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:
Brackets for Pool Auto Jo16.ods (63.7 KB)
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"
Garry
Quick responses: