In case you’re bored stiff during your holiday, here’s a nice Excel challenge for you all.
In my newsletter for May 2019 I showed you a trick to get all possible combinations of two lists. This time I have a related problem. Suppose I have 10 ballot tickets. My job is to draw 5 random tickets from that set of 10 repeatedly until I have drawn all possible combinations:
The challenge I would like to give you all:
Please use any technique in Excel to list all possible unique combinations of ticket numbers drawn this way. Note that the order in which the tickets are drawn makes no difference and that each number can only be drawn once per set of 5. This means that a draw of (1, 2, 3, 4, 5) is considered the same as (5, 4, 3, 2, 1) or (2, 1, 3, 4, 5).
Do you want to be able to rename your Excel tables and columns without breaking existing PowerQuery queries? Do you always change the default name of a new table? Want to have quick navigation to your tables in your workbook?
Use my free TableTools add-in
It’s been quite a while, but finally I got round to publishing a new article on my site.
It is a nice trick to make full use of the Data Validation’s Input message option, without the popup message covering your sheet.
So not like this:
But like this: Have a look at the article on my website
Based on an idea by fellow Excel MVP Frédéric le Guen I have created a new small Excel add-in which makes your life using tables in Excel slightly easier. It sports a ribbon tab which contains a drop-down containing all tables in your worksheet so you can quickly jump to them and another one which displays all columns in the current table. Both items are also available in the cell right-click menu. The former when you right-click on a cell not in a table, the latter when you click on a cell within a table.
It also has an enhanced “Insert table” dialog which enables you to enter the name of the table and has the Headings checkbox turned on by default no matter what:
It seems the busier one is, the more work gets done. I’ve published a nice article today, in which I explain how I created a Wheel Of Fortune made entirely in Excel some years ago. This is what it looks like: