Dynamic plot order of series

[Nexter06]

I have several series of data in stacked column chart and I am trying to achieve that plot order will be dynamic due to some values, but in =SERIES plot order must be integer and I can´t find other way to do that.

Do you have any idea or advice? Thanks for help.

 

[thisoldman]

One way to do this is to have one data block containing your data and a second block to contain the charted values.

This example is set up to chart the data in F4:I9. Three different series orderings can be selected by placing a value, 1, 2, or 3, in Cell B1. There are three combinations of the six possible that I don't include.

This is my setup.

Excel 2012
	A	B	C	D	E	F	G	H	I
1	Selector	3
2
3	Original data					Charted data
4		Jan	Feb	Mar			Jan	Feb	Mar
5	Alpha	50	25	31		Gamma	34	29	42
6	Beta	43	50	49		Alpha	50	25	31
7	Gamma	34	29	42		Beta	43	50	49
Sheet1

The Selector cell, is the input for which of the three series orderings I want to display.

Notice the items to select in this CHOOSE formula form a partially repeating series. Cell F5, copied and pasted down:
=CHOOSE($B$1+ROW()-5, $A$5, $A$6, $A$7, $A$5, $A$6)

Cell G5, copied and pasted through I7:
=INDEX($B$5:$D$7, MATCH($F5, $A$5:$A$7, 0), MATCH(G$4, $B$4:$D$4, 0))

I hope I have explained this clearly.

 

[Nexter06]

It's very helpful. Thank you!