Stephan
Member
Hello my latest dashboard attached.
XL Dashboard for employee Quals/Skills/Competencies, useful for establishing at a glance who needs training, or project managing staff for projects/jobs/task.
Its pretty slick no macros all formulas XL2003, using Data Validation, Defined Name lists, Countif & Sumproduct, effectively, if how I've achieved 1st tab is mystery, see SURVEY 4TH TAB and look at columns AZ:DI.
"SELECTOR" 1ST TAB: Select Quals/Skills/Competencies using DropDownBoxes in COLUMN AJ2 to AJ30. To clear these, select cells, and press DELETE, then make new selections, which will appear immeadiately to your left & below.
Criteria matches are displayed in A2:AG30.
Criteria number summary in Columns A31:D46
Chart Occupation in Columns E31:AG46
The other tabs:
Basically I started with Trades list 4th tab >
Survey form 3rd tab & to collate populated data >
Stats summary 2nd tab is overall summary of employees in survey from 3rd tab > Selector 1st your criteria requirements of specific quals/skills/competenices matched to name list.
Results =1st & 2nd tab
Master info database = 3rd tab
Master lists for DropDownBoxes = 4th tab
I've done this on a very large scale for industry previously, this is a concise version.
Hence the POSTCODE column is for GEO REGIONAL MAPS and/or HEATMAPS via GOOGLE FUSION TABLES. Easily done very effective.
Any Questions? Also yes you could achieve something similar with AutoFilter, Pivots Tables or Slicers but I think this XL Dashboard is more user friendly, and I like to keep RESULTS data seperate from the MASTER database. Sounds like a quote from StarWars!
Cheers Stephan
XL Dashboard for employee Quals/Skills/Competencies, useful for establishing at a glance who needs training, or project managing staff for projects/jobs/task.
Its pretty slick no macros all formulas XL2003, using Data Validation, Defined Name lists, Countif & Sumproduct, effectively, if how I've achieved 1st tab is mystery, see SURVEY 4TH TAB and look at columns AZ:DI.
"SELECTOR" 1ST TAB: Select Quals/Skills/Competencies using DropDownBoxes in COLUMN AJ2 to AJ30. To clear these, select cells, and press DELETE, then make new selections, which will appear immeadiately to your left & below.
Criteria matches are displayed in A2:AG30.
Criteria number summary in Columns A31:D46
Chart Occupation in Columns E31:AG46
The other tabs:
Basically I started with Trades list 4th tab >
Survey form 3rd tab & to collate populated data >
Stats summary 2nd tab is overall summary of employees in survey from 3rd tab > Selector 1st your criteria requirements of specific quals/skills/competenices matched to name list.
Results =1st & 2nd tab
Master info database = 3rd tab
Master lists for DropDownBoxes = 4th tab
I've done this on a very large scale for industry previously, this is a concise version.
Hence the POSTCODE column is for GEO REGIONAL MAPS and/or HEATMAPS via GOOGLE FUSION TABLES. Easily done very effective.
Any Questions? Also yes you could achieve something similar with AutoFilter, Pivots Tables or Slicers but I think this XL Dashboard is more user friendly, and I like to keep RESULTS data seperate from the MASTER database. Sounds like a quote from StarWars!
Cheers Stephan