Excel Hash 2020 – Dynamic Playoffs Table - EBOOK VBA EXCEL

EVBA.info ( Ebook VBA Excel ) Welcome EVBA.info - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Free Courses, Living Skills ...

Excel Hash 2020 – Dynamic Playoffs Table

The four mystery ingredients we’ve been given are:
1. Icons
2. Dynamic Array Functions*
3. The XOR Function
*Dynamic Array functions are available in Office 365 only.
Our task was to come up with an Excel tool that used all four ingredients in an integrated way. We were free to choose the data and could use other tools from the Excel pantry except VBA!
You can see my entry in action in the animated image below:
Note: If the image below isn’t animated, click here to see it on the blog.

Dynamic Playoffs Table

My entry is a competition table that identifies which teams need to play a third match from a list of teams that have already played two matches.
• Teams that won both matches automatically qualify for the semi-final – Sharks and Tigers are an example of that

• Teams that lost both matches are out – that’s Roosters

• Teams that only won one match play a third to qualify for the semi-final – that’s Whales, Snakes, Lions and Unicorns
Based on these rules I’ve used the XOR function to determine which teams require a third match, which you can see the results of in column G below:

It then extracts them to the ‘Teams in Round 3’ table in columns J & K using the dynamic array FILTER function and linked pictures which pick up the team icon from column C.

Components

1. Icons have been inserted in column C for each team’s logo

2. The XOR formula in column G returns TRUE where a team won one match and lost one match. Teams that won both or lost both return FALSE. This is inline with the competition rules above.

3. The dynamic array FILTER formula in column H returns the list of teams where XOR returned TRUE.

4. Dynamic named ranges lookup the team name returned by FILTER in column J and return the cell containing that team’s logo. There is a dynamic named range for 4 teams, as you can see in the name manager below:

1. Linked Pictures were copied from the logo cells in column B and pasted into column I.

IMPORTANT: The cell containing the picture (image object) must be as big or bigger than the picture, as it’s the cell you’re copying, not the image object.
The link references for each picture were then replaced with the dynamic named ranges for each team:

Competitors and Voting

My fellow Excel MVP competitors are:
• Bill Jellen - MrExcel
• Leila Gharani
• Jon Acampora - Excel Campus
• Oz du Soleil - Excel on Fire
#evba #etipfree #eama #kingexcel