Forum Discussion
Chart graphs that automatically change depending on the subject
In the mixed vertical graph,
If the subject score is 0, I want to exclude it from the graph,
and automatically add it to the graph when the subject score is added.
Of course, the spacing is automatically adjusted, and the subject name is also adjusted.
There are 4 horizontal items, and I enter a2~d2.
There are 10 people in each item.
The person graph can increase or decrease depending on the score.
Can you tell me the specific filter function formula including the name manager?
The example table is for the case where there is no score for brother1 and brother4.
A | B | C | D | E | F | G | H | I | J | K | |
1 |
| father | mother | child | infant | brother1 | brother2 | brother3 | brother4 | brother5 | brother6 |
2 | Type a2 | 42.2 | 39.1 | 47.9 | 36.1 |
| 22.9 | 56.3 |
| 57.3 | 36.1 |
3 | Type b2 | 53.0 | 47.0 | 43.3 | 35.0 |
| 30.8 | 51.7 |
| 55.8 | 35.0 |
4 | Type c2 | 59.4 | 65.6 | 59.5 | 30.5 |
| 32.3 | 55.0 |
| 59.0 | 30.5 |
5 | Type d2 | 43.1 | 61.9 | 54.7 | 33.5 |
| 34.1 | 40.0 |
| 57.1 | 33.5 |
Have you tried the demo workbook that I attached to my previous reply? It should do exactly what you want.
6 Replies
This would be (relatively) easy if the 4 types were the series, and the scores for a type the series values.
The problem here (if I understand it correctly!) is that the series of the chart are the family members, so the number of series varies. In the example, the series for brother1 and brother4 should be removed.
The number of data points for each series is fixed: the scores for each of the 4 types.
We can solve it by creating a Worksheet_Change event procedure:
Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long If Not Intersect(Range("B2:K5"), Target) Is Nothing Then Application.ScreenUpdating = False With Me.ChartObjects(1).Chart ' Delete the existing series For i = .SeriesCollection.Count To 1 Step -1 .SeriesCollection(i).Delete Next i ' Add series for non-blank columns For i = 2 To 11 If Cells(2, i).Value <> "" Then .SeriesCollection.Add _ Source:=Cells(1, i).Resize(5), _ SeriesLabels:=True End If Next i ' We must specify the category labels again .FullSeriesCollection(1).XValues = Range("A2:A5") End With Application.ScreenUpdating = True End If End SubSee the attached demo workbook (I hope it will not be removed by the forum software).
Thank you for your reply.
I have a few more questions.
The maximum number of family members is 10,
and only the members with scores should be shown.
Just as brother1 and brother4 were not on the score sheet, there may or may not be scores in the middle.That is, only members with scores should be
shown on the graph and reflected flexibly in the legend format.In the case of brother1 and brother4, since they did not have scores,
they were omitted from the graph and legend format,
but they should be added to the graph and legend format again when they have scores.And if one of the middle siblings is missing, the graph will also disappear, and the spacing should be adjusted automatically.
According to what you said,
the target is not added, the legend format is not automatically reflected, and when there is no score, the graph becomes blank.I am very grateful for your kindness.
Have you tried the demo workbook that I attached to my previous reply? It should do exactly what you want.
How about named ranges and filter functions:
=FILTER(A2:K2, A3:K3<>0)Thank you for your reply.
I registered the filter function you told me in the name manager.
Then, after selecting data in the graph,
Click Edit on the legend item (series) and insert the name manager item into the series value.
I did that first, but it doesn't work well.
Can you tell me a more specific formula and where to insert it?
Thank you for your kindness.
I hope this helps as a solution! Let me know if you need more details or assistance.
In B1:
=SEQUENCE( ,10)
to make numbering.
In B12:
=FILTER(B1:K1,B7:K7>0)
to filter index which total is > 0.
In B13:
=CHOOSECOLS(B2:K6,B12#)
Test:
