• 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

    Hui...

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

XL Button Navigation instead of Tab

Stephan

Member
Impressive simple Front Sheet VB navigation with detailed XL Dashboard.

Versions attached, frontal navigation is the more sophisticated MACRO/VB version:

BUTTON navigation: Modern graphic button navigation, hence 2 buttons on opening front page:
Left: Job>City.
Right: JobSub>City/Ind.

Individual search pages display BACK button to return to front page.

This file uses MACRO VISUAL BASIC, hence EXCEL MACRO SECURITY needs editing in EXCEL to use this specific file, instruction method similar to this:
Tools, Options, Security, Macro Security, Security level > Low, OK, OK. Close file then Reopen File, and navigation macros will work as intended.

FILE NAME: Macro/VB File within ZIP: UKSOC2016##VB.xls.zip. FILE SIZE UNZIPPED: 1.8MB.


Macro/VB file navigation as pictured below, unzip file and adjust excel macro security to use, as advised directly above.

SocStatsFrntPg.jpg


Statistical Data
is presented with a Visualisations Dashboard with Dynamic Charts / Gauges / Images / Rankings of UK OCCUPATIONS/SUBGROUPS STATISTICAL DATA 2016 per CITY/INDUSTRY.

Very useful tool detailing occupation totals per cities/industries, far more useful then opinion or perceived general consensus of regions/industry/jobs. Adjust Excel: View/Zoom for optimum use.

Version attached:
Macro/VB Navigation File within ZIP: UKSOC2016##VB.xls.zip.

Occupation SOC Code per Region/City: 1st tab selectable 'aqua' combo boxes: right of> O5 & O10.
Legend: O5 = OCCUPATION & O10 = REGION/CITY. These update main chart or sub totals, % & gauge.

IMPORTANT NOTE: Occupation: O5 new selections refresh available data per CITY: O10, hence after new selection of OCCUPATION: O5, make new selections of 'available' CITY: O10 to 'refresh applicable' data totals.

1st tab dynamic objects:
City =P12. %=O17. Rank =O15. Gauge =P14. Weighting =O19. Chart =ColA-N. Rank Scroll =P22/24.

SOC.jpg



Occupation SubGroup SIC Code per Region/City: 2nd tab 'aqua' combo boxes: right of> O6, O11 & P11.
Legend: O6 = OCC SUBG, O11 = INDUSTRY & P11 = CITY. These update main chart or specific sub totals, % & gauge.

IMPORTANT NOTE: Occupation: O6 new selections refresh available data per INDUSTRY: O11 & CITY: P11, hence after new selection of OCCUPATION: O6, make new selections of 'available' INDUSTRY: O11 and CITY: P11 to 'refresh applicable' data totals.

2nd tab dynamic objects:
IND=O14, %=O15, Rank=P5. Img=O16. City=P14, %=P15, Gauge=P16. Chart=ColA-N. SubG=P3,%=P4. Rank=P22/P24.

SIC.jpg


NOTE: Figure before OCCUPATION in combo box is not total, either Standard Occupation Code or Standard Industry Classification.
1st tab dynamic objects: City=P12. %=O17. Rank=O15. Gauge=P14. Bar=O19. Chart=ColA-N. Ranks=P22/24.
2nd tab: IND=O14/O15, Rank=P5. Img=O16. City=P14/P15, Gauge=P16. Chart=A-N. Sub=P3/P4. Ranks=P22/P24.

1st tab OCCUPATIONS are categorised per STANDARD OCCUPATIONAL CODES, derived from JOB TITLES.
2nd tab SUB GROUP OCCUPATIONS are categorised per STANDARD INDUSTRIAL CLASSIFICATIONS, derived from BUSINESS PURPOSE.

Initial impression is how to organise data visualisation, especially with numerous categories of occupation per region, and industry category.

Hence prepared Excel Dashboard, all in formulas, Excel 2003 for backward compatibility, formulas used: Index, Match, Countif, Defined Named and Data Validation.
 

Attachments

  • UKSOC2016##VB.xls.zip
    756 KB · Views: 8
Back
Top