• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

XL Dash Evolution > Index Match & Data Validation Boxes


Hello, article on Excel dashboards evolution & use of 3D index match in UK, pretty concise, all file hyperlinks are near btm of this post.

Dashboards are at brilliant data visualisation, hence only displaying certain fields and not all data, away from the master source.

Database master sources, are described differently depending on their uses and who has access to it.

Less then 20 years ago, Dynamic Alternatives to AutoFilter needed establishing for seperate non financial summaries.

Static copy and paste, wouldn't be accurate, around Millennium, working professional examples of business databases/dashboards from Excel Experts were starting to emerge, with varying approaches, they can mainly be categorised as follows:

Vlookup and Defined Names: Approach difficult to repriclate with clarity on large scale. ie, is that row a YES or NO wasn't this obvious, as Vlookup was a cell by cell approach. Occassional spreadsheets or already established sheets used this effectively, and at the time, seperate sorts of data was appreciated.

Bespoke Databases were designed specifically for corporate international/national businesses, and many considered Access, few persons proficient in VB designed some impressive databases with differing levels of automation, such as new orders, etc. Overtime it became obvious Access Program wasn't as sophisticated as it appeared at 1st glance, for example the Data Validation technique was flawed, and would require VB bespoke edit, which is less then obvious with valid fields or constraints.

Visual Basic: Few persons proficient, unless datasets are generic VB could need further future edit, which revisiting some time later, may be difficult.

Index Match and CountIf: Brilliant method validating if each row meets criteria, and applicable fields are displayed in seperate sheet with If/Rows/Index.

However even though this met requirements, users unfamiliar with formulas thought datasorts were incomplete data lists or they could edit them then unprotected sheets needed further edit to correct formulas unitentionally typed over, hence Graphical User Interfaces were considered, so choices could be defined without individually formula edits.

UserForms are time consuming and ackward to implement, and once implemented genrally aren't generic for future edits or duplication for other uses, hence even though initially considered, many end users preferred working with worksheets.

Macro Buttons were the next consideration, as within worksheet, but looked unprofessionally and were often unintentionally moved around.

Data Validation Boxes and Defined Names were the final consideration, introduced to Excel late 1990s, initially not seriously considered drop down box wasn't obvious until cursor was directly over specific cell.

DV Combo Boxes uses/applications probably unappreciated initially, until it occured to use DV boxes with Index Match to amazing graphic effect for GUI Dashboards!

DASHBOARDS ORIGINS: occured to me 2003-2010, compiled file, results were seperate sheets, needed front sheet GUI, but as per the time had certain level of automation, including speech, automatic emailing, which of course was VB element. But at a glance looks basic compared to Dashboard.

Dashboard frontsheets were then created with DATA VALIDATION BOXES for selections, fields meeting this criteria in MASTER DB WORKSHEET were identified using INDEX MATCH, applicable row datasets display in dashboard frontsheet. Dynamic Charts were put into effect using seperate datasorts, used as data source. Once such was established the Excel Experts wanted to keep intectual ownership, or experienced Industry persons protected worksheets, hiding tabs with VB.

Dashboard examples inc Dynamic data/images/objects, either in formula or VB, dataset UK OCCUPATIONS per CITY and/or INDUSTRY:

FORMULA version 1.8MB or VB front end version 2MB

Dashboards became more involved, more dynamic charts, scroll bar ranks, gauges, dynamic images. And further applications of Index Match considered, such as 3D use of data due to MONTHLY TABS typically used in Employee Schedules , or Index Match used for filtering more generic Classified datasets.

INDEX MATCH 3D examples:


or a much smaller scale example see:

SALES#CODE#.zip: 1MB. BUSINESS SALES DB in FORMULA with VB front end see 3D tab: PROMO.