June 2018 | Peter Bartholomew
One striking feature of the non-macro (xlsx) version of the Template is the use of dynamic flags to reinforce the message from the Group Standings tables and the Knock Out stage. Here we show you how to lookup pictures using formulas in Excel to enliven your spreadsheets.
Figure 1: The Group A Standings Sheet
As you enter your predictions so the national teams and their flags move up or down the rankings. Do you know how this can be achieved? The master copy of the flags is held as a range on a hidden worksheet, the columns shown in Figure 5 being named ‘country.list’ and ‘flags’ respectively. To create a copy, select the cell behind the picture and then copy and paste the image as a picture.
The options for redefining the image to be shown on a given flag, such as the four flags to the right of Figure 1, are very limited. By selecting the flag and typing a formula such as =Flags!$F$26 into the formula bar it is possible to change the image. This, as it stands, offers no flexibility for changing the image on the flag. The only other option is to use a defined Name. Thus the flag showing Russia in the figure is set to =FlagSelect.1st
The options for redefining the image to be shown on a given flag, such as the four flags to the right of Figure 1, are very limited. By selecting the flag and typing a formula such as =Flags!$F$26 into the formula bar it is possible to change the image. This, as it stands, offers no flexibility for changing the image on the flag. The only other option is to use a defined Name. Thus the flag showing Russia in the figure is set to =FlagSelect.1st
Figure 2: Range with flags
(c) The trick then is to let the name hide the complexity of a formula by referring to:
FlagSelect.1st: = INDEX( flags, INDEX('Group A'!index.order, 1 ) )
where the local name ‘index.order’ is an array built to show the current ranked order for the four teams in the Group. That ranking is, in turn, used to identify the cell in the ‘flags’ range which holds the required flag.
Although this gives a very attractive dynamic feel to the workbook, it is very resource intensive and should be used with caution. I have seen it advised that one should probably limit the number of dynamic pictures of to a number in the region of ten. The current workbook contains 84 such images (6 for each group and 36 at the knockout stage) so is well outside the recommended bounds. The resulting delay, of a second or so, is noticeable when developing the workbook but we decided that an overhead of a couple of seconds whilst predicting a score should not be a major issue. If you are deciding on your predicted results that fast, you probably should be giving the problem a little more thought!
In future articles I have in mind a demonstration of the way in which 4x4 arrays holding the match results for each group may be manipulated to give the standings table and to determine which teams are predicted as progressing to the knock-out stage.
Download the Excel prediction template here:
https://www.spreadsheet1.com/fifa-world-cup-2018-russia-free-prediction-templates-for-excel.html
FlagSelect.1st: = INDEX( flags, INDEX('Group A'!index.order, 1 ) )
where the local name ‘index.order’ is an array built to show the current ranked order for the four teams in the Group. That ranking is, in turn, used to identify the cell in the ‘flags’ range which holds the required flag.
Although this gives a very attractive dynamic feel to the workbook, it is very resource intensive and should be used with caution. I have seen it advised that one should probably limit the number of dynamic pictures of to a number in the region of ten. The current workbook contains 84 such images (6 for each group and 36 at the knockout stage) so is well outside the recommended bounds. The resulting delay, of a second or so, is noticeable when developing the workbook but we decided that an overhead of a couple of seconds whilst predicting a score should not be a major issue. If you are deciding on your predicted results that fast, you probably should be giving the problem a little more thought!
In future articles I have in mind a demonstration of the way in which 4x4 arrays holding the match results for each group may be manipulated to give the standings table and to determine which teams are predicted as progressing to the knock-out stage.
Download the Excel prediction template here:
https://www.spreadsheet1.com/fifa-world-cup-2018-russia-free-prediction-templates-for-excel.html
Backstage UI Powered By Ribbon Commander
Our World Cup 2018 macro-free spreadsheet has a Backstage UI with clickable hyperlinks that navigate to articles in our website. Hover over images to view the URLs.
This complex custom user interface was designed using the Ribbon Commander (RC) framework. Download our macro-enabled workbook to find-out how easy it is to generate a dynamic ribbon UI with VBA alone and generate the XML behind this Office user interface using Ribbon Commander.
Please feel free to adapt this Ribbon Commander code to suit your Office Excel projects!
This complex custom user interface was designed using the Ribbon Commander (RC) framework. Download our macro-enabled workbook to find-out how easy it is to generate a dynamic ribbon UI with VBA alone and generate the XML behind this Office user interface using Ribbon Commander.
Please feel free to adapt this Ribbon Commander code to suit your Office Excel projects!
Download
Flag demo file (XLSX) for Excel 2007 or later |
Workbook with dynamic Backstage UI (XLSM) Powered by Ribbon Commander (VBA & XML code) |
What Do You Think?
I would love to get your input on how to make this Excel World Cup template better. If you notice any bugs or have a new feature request or would like to suggest modifications to existing features, please let me know in the comments section below. I look forward to your feedback :)
