Automate series addition/removal in a clustered bar chart

Good day,

On a clustered bar chart is there anyway to automate the removal and addition of a series (I have only seen solutions for adding and removing categories) including in the legend? I have tried using Offset but the chart forgets the named range and only remembers the cells. I also tried recording a macro which didn't work either now I am losing patience.

I would prefer not to use a macro because then I would have to explain macros to colleagues who hardly understand formulae but if that is the only solution.

Thank you,
Andrew