jules meredith
New Member
Hi,
I have a football league spreadsheet with multiple seasons. On one sheet I have an interactive chart that displays 'goals for' and 'goals against' for all matches played in a season. The interactivity comes in the ability to select a specific team and the season.
My dilemma is that after year x the league was reduced from 22 teams to just 20. Therefore the data range needed changes from 42 columns (1 column for each home & away game) down to 38 columns. Currently if I select a season after year x, the chart displayed then zeros out for the last four columns and also shows 39,40,41 & 42 in the x axis.
Is there anyway to switch the charts data range used from say "B1:AQ2" to "B1:AM2" automatically, when a specific combobox selection is made thus removing the unecessary trailing zeros and x axis data from the new chart.
NB I have used the "NA()" function in my formulas which does remove the trailing zeros but the redundant info on the x axis remains.
Any help on this will be very much appreciated.
I have a football league spreadsheet with multiple seasons. On one sheet I have an interactive chart that displays 'goals for' and 'goals against' for all matches played in a season. The interactivity comes in the ability to select a specific team and the season.
My dilemma is that after year x the league was reduced from 22 teams to just 20. Therefore the data range needed changes from 42 columns (1 column for each home & away game) down to 38 columns. Currently if I select a season after year x, the chart displayed then zeros out for the last four columns and also shows 39,40,41 & 42 in the x axis.
Is there anyway to switch the charts data range used from say "B1:AQ2" to "B1:AM2" automatically, when a specific combobox selection is made thus removing the unecessary trailing zeros and x axis data from the new chart.
NB I have used the "NA()" function in my formulas which does remove the trailing zeros but the redundant info on the x axis remains.
Any help on this will be very much appreciated.
