Dynamic X Axis

JJ,

you need to calculate the list of values you want to show, create a list of these values only, create a dynamic range that grows and shrinks automatically and then chart that dynamic range.

Clear as mud? See attached with formulas doing the work. No array formulas!!

cheers

Ok,

Works just how I need it to but I am stumped on 1 thing.

When I insert>name>define name and set ChartLabels to:

='Data (ALL)'!$O$3:INDEX('Data (ALL)'!$O:$O,COUNTA('Data (ALL)'!$O$3:$O$61)-2,1)

It seems that its Indexing to much as it highlights beyond the displayed text?

EDIT:

The above problem still exists, but I've found something else.

On the attached example if you change one of the catagories from 0 to a number, eg Orange.

The graph only has a defined range, not a dynamic one as its set and cuts data off?

attached example

attached where?

It seems that its Indexing to much as it highlights beyond the displayed text?

hard to tell without seeing your data.

On the attached example if you change one of the catagories from 0 to a number, eg Orange.

The graph only has a defined range, not a dynamic one as its set and cuts data off?

In the file I attached you can change any zero to a number and it will pop up in the chart.

JJ, didn't get to download your chart, but realised I have to refine my INDEX skills.

replace the range definition for ChartLabels with the (volatile) Offset variant

=OFFSET(Sheet1!$E$1,1,0,COUNTA(Sheet1!E:E)-1,1)

New take. Awake now. A few hours of sleep can do wonders.

formula for E2 and down

=IF(ROW(D2)-ROW(D$2)+1>COUNT(D$2:D$10),0,INDEX(A:A,SMALL(D$2:D$10,1+ROW(D2)-ROW(D$2))))

Index formula for range name ChartLabels

=Sheet1!$E$2:INDEX(Sheet1!$E$1:$E$10,MATCH("zzz",Sheet1!$E$1:$E$10,1),0)

see attached.

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.