even more Dynamic Charting?

[Stephan]

XL Dashboard of ALL UK Occupations 2016s: Dynamic Charts on 1st tab & 3rd tab..

Objective to make 1st tab even more Dynamic, hence not to display blank columns of City per Industry, as seen in less sophisicated 3rd tab. Difficult in 1st tab due to additional header, seen in 9 categories at bottom of chart, industry categories.

1st tab combo boxes: 06 = Occupation, O11 = Industry & P12 = City.
Data is within 2nd tab Table1: A1:FY92

3rd tab combo boxes: 05 = Occupation & 010 = City.
Data is within 4th tab Table1: A1:EL370

It is alot of data, but as you can guess this isn't my 1st spreadsheet and this is an excellent 1st go Data Visualisation for this dataset, and surely there's an unonerous solution to this a table with 2 headers scenario that isn't too radical or divergent.

Sounds like a quote sequel gushing from that Kate Winslet film, or that new Scarlett Johansson epic Ghost in the Shell, but on that theme, for something a bit less then your ordinary theme, I'd imagine there are solutions that are easy to implement and work effectively.

 

[Hui]

I would add a Pivot table and a Pivot chart to the model
The pivot Table will need to be based on the Transposed Range Table1, Rows 94:96, after putting individual values in Row 94
Then applying filters to only show field values > 0



See attached file:

This could be done with formulas but I don't have time today to do that

 

[Stephan]

I would add a Pivot table and a Pivot chart to the model
The pivot Table will need to be based on the Transposed Range Table1, Rows 94:96, after putting individual values in Row 94
Then applying filters to only show field values > 0

View attachment 38969

See attached file:

This could be done with formulas but I don't have time today to do that

Hello. That is what I want to achieve.

Excel 2003 is only XLS. Hence original file is all formula, would appreciate formula, to same end as your project image.

Pivot Tables basic feature of Excel 2003. Academic on a budget, there is some!

Cheers Stephan

 

[Stephan]

XLSX convert to XLS, Pivot Tables don't work, but approach seems valid.
Attached file is how it appears in Excel 2003, incomplete, perhaps viable edit.

Hence in EXCEL 2003, how to apply the PIVOT TABLE to Transpose sort ROW 95-96 for item 1, 2 & 3 below?

As done here:
1. PIVOT SORT of just applicable INDUSTRIES per selected OCCUPATION > ROW 103:282.
Perhaps just is part of the Pivot Table, but in Excel 2003, it just appears as Transpose formula within active list. Didn't think an active LIST works with array formulas in Excel 2003.

2. PIVOT SORT of above Industries excluding CITIES zero value > ROW 286+
Perhaps just is part of the Pivot Table, but in Excel 2003, it just appears as plain pasted once data, hence fields don't update.

3. PIVOT CHART of just dynamic data, in Excel 2003, source data just appears as: =Table1!$A$286:$C$338. Which doesn't look very 'Dynamic' to me!

 

[Stephan]

Hi Hui! Made progress, Table1 Formula edit removing duplicates, basically observed what Pivot Table was doing & made manual Formulas.

Now all I need to do is make DYNAMIC CHART from this RANGE:
D285:F567

Currently the 2 row header can't figure a formula to work with this using Dynamic Chart. Already easily achieved in "OccupationUK" as just 1 row header.

This post attachment is a works in progrss, the final version this will dynamically change depending on choices made in 1st tab, as per 1st attachment.

 

[Stephan]

Further edit for Formula based Dynamic Table/Chart version for Excel2003:

Dynamic Chart to be edited to work with 2 Row header in file below:

See File Zip: http://www.hkrebs63.karoo.net/files/DynamicTable&Chart_EDIT.zip

Dynamic Table within File: UKSOC2016##.xls

Dynamic Data Table Tab: Table1

Dynamic Range: D289:F571

OccupationsSubGroupUK Tab shows Dynamic Chart that includes column spaces, above edit to exclude column blanks.

Dynamic Chart within File: DynamicChartRanges#VERT.xls

 

[Stephan]

Hello! Solved it, all in formula! File attached in Zip: UKSOC2016##VB.zip. Narrowed it down > 2 Defined Names ref'd in hidden tab DynamicChart1 Source: Value & Label, that's it!
Basic example also attached: DynamicChart#2RowHdr3Col##.xls

1st defined name just needed 2 column ref edit, and 2nd defined name just needed Counta changing to Count, and end value to -0.

Defined Names:
=OFFSET(DynamicChart1!$A$2:$B$2,0,0,COUNTA(DynamicChart1!$B:$B))
=OFFSET(DynamicChart1!$C$2,0,0,COUNT(DynamicChart1!$C:$C)-0)

Chart > Source Data: right click Chart > Source Data > Series: to see actual Defined Names
Values: Series Defined Name
Category X Axis Lables: Value Label

UKSOC2016##VB.xls:
Dynamic Chart1 tab changes Dynamically depending on selections made on tab OccupationSubGroupUK in cell P6.

Compare the 2 charts, version on tab Dynamic Chart1 makes exclusion of all blank entries, the other version 'less' Dynamic within tab OccupationSubGroupUK shows all feasible cities, good general picture. Which makes 1 disadvantage apparent of the more dynamic , 'some' category headings with few fields get squashed and overlap making illegible!

'Ultra Dynamic' 2 headers and dynamic charts are feasible in Excel 2003.
Hence DBL HEADER dynamic table can be used in dynamic chart with blanks (OccupationSubGroupUK) or without blanks (hidden tab DynamicChart1), and yes it can, the desired effect will depend on the dataset.

 

[Stephan]

Dynamic Chart 1 series examples data arranged HORIZONTAL or VERTICAL for 1 or 2 (DBL) Header.