Lookup or Index & Match Maybe?

Here you go. To have a separate "diminishing list" for each Bracket (sheet), you really need a separate set of source data. I've added a second set and named them Table1 and Table2.

I've also changed the formulas on that sheet to ones that cause numbers to disappear in the first column and the list to "shorten" in the right column. This will eliminate all the untidy "spaces" your other method was causing in the drop downs themselves.

Also, I've redone the Table1 and Table2 name definitions so they are dynamic. You won't have blank spaces at the END of the drop down either, it will always show only the numbers visible in the second column for each bracket.


If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Hi JoeJaycee,

Jerry really gave you a lot of excellent code to work with and you only need to change this one bit to get your bracket2 problem sorted out.

In the Name Manager change this line for Tables2:

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

@JBeaucaire,

Thank you for helping out here. You work, is as always, absolutely outstanding!

Re:

So when you double click on A5 or A7 for example, I need it to copy that value to D6

You could enter a formula in D6 to Show what is in A5 or A7. Since you have different values in A5 and A7,we won't know which one you would prefer to show in D6.

Maybe you could consider A5 to show in D6, A7 in D7, A9 in D10 and A11 in D11.

Ok, so BOTH sheets will use the same "pool" of tables in each round? Since each round means the same tables would be used again, I created a separate "pool" of tables for each round. Currently, each round has 40 tables, you can shorten each group to just the number of tables MAX needed for each round to keep the dropdowns tidy.

I've also added a "diminishing drop down" for your teams, too. As you add Teams to the TEAM sheet, the "TABLE" sheet will grab those new ones as well.

Lastly, I've added the "double-click" event for you. Any sheet that has the name "*Bracket*" then the macro in the Thisworkbook module will trigger a movement based on which cell you double-click on. I've also added a RESET button to those sheets.

You need a group of tables 1-40 for round32 on Bracket1, then ANOTHER group of tables 1-40 for round32 on bracket2?

Just add another set of columns like we did for table1, then name that Table2 and use Table2 on Bracket2. Simply stated, you need to create a diminishing table with a unique name that you can use for each "unique" requirement.

1) Single set of tables used on both sheets.
2) Table list diminishes as tables are chosen
3) Team Names in column A diminish as team names are chosen
4) Double-clicking a team name cell will transfer winner to next round and clear the used table
5) Reset button to start anew

Joe, am I helping or just doing it all? This thread has strayed far from it's post #1 question, and each post is you ordering new features added. The forum isn't, at it's best, just meant to be "get coding done for free here". This is hopefully meant to be a learning process for you. If you're really just looking to get an entire project done for you, perhaps a paid consultant is a more honest approach.

So, open the workbook, start reading through the code and doing some homework. you need to understand what you have so far if you're going to be able to add to it and/or tweak the existing features if you start moving things around. You should at least be trying to do some of this and/or learn from this.

[END SPEECH MODE]

Try turning on the macro recorder and let it record you doing something specific you want to add, something you can see in the recorded code for what it is. Then try to add that to the existing code. The code that is doing things automatically is in the ThisWorkbook module. The reset code is in Module1.

Ok, for charity, huh? (chuckle)

In the ThisWorkbook module, add this line:

Please Login or Register  to view this content.

If you want to mockup this sheet in your workbook to "printout", I can make that sheet print when you select table numbers.

This sheet to print needs to be in your workbook. So go ahead and mockup your sheet there and post it.

I had to fix an issue with the "moving the old table" because the cell we moved down to was STILL in the "count range" we were using to determine if the table was available or not. So I created a new spot in column L to indicate the prior tables used, they match row for row.

Be sure you have filled in BOTH team names before you select a table because it won't check before it prints automatically. Lots more "Offset" functions in the new macro in ThisWorkbook that is handling the auto-printing.