Excel Hash 2020 – Dynamic Playoffs Table - EBOOKS AND TIPS FREE

EBOOKS AND TIPS FREE

Ebooks and Tips free for you.

Trang

Saturday, May 23, 2020

Excel Hash 2020 – Dynamic Playoffs Table

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
  4. Linked Picture(s)
*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.
excel hash
 

Download Workbook


Download the Excel Workbook and follow along. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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:
excel hash 2019 dynamic team table
 
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:
     
    dynamic named range for 4 teams
     
    1. Linked Pictures were copied from the logo cells in column B and pasted into column I.
       
      linked pictures copied from logo cells
       
      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:
      link references replaced with dynamic named ranges
       

      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
      📤How to Download ebooks: https://www.evba.info/2020/02/instructions-for-downloading-documents.html?m=1

      No comments:

      Post a Comment