dynamic charts

[mcgew]

I've created a dynamic column chart, but the range always contains the first blank cell, and so there's a blank column at the end of the chart.
Is there anyway i can get the range to go stop at the last cell with a value in it?

 

[PaddyD]

there are many ways! post back with a bit more info on your data & ehow you have set up your dynamic named ranges. Include the formulas you are using.

Paddy

 

[mcgew]

The data is set up in rows, with each column as a date.
In the chart the date is on the x axis and the data categorie values on the y axis.
I defined names as:
XDate = OFFSET(daily!$D$2,0,1,1,COUNTA(daily!$2:$2))
and
YBuildTarget = OFFSET(daily!$D$5,0,1,1,COUNTA(daily!$5:$5))
and for the series formula entered:
=SERIES(daily!$D$5, 'main daily.xls!'XDate,'main daily.xls!YBuildTarget, 1)

Thanks
Grace

 

[PaddyD]

Grace,

You are offsetting by an extra column...try these:

Xdates:
=OFFSET(daily!$D$2,0,0,1,COUNTA(daily!$2:$2))

YBuildTarget
=OFFSET(daily!$D$5,0,0,1,COUNTA(daily!$5:$5))

notice the zeros in the 'column' arguments for the offsets.

Paddy

 

[mcgew]

I've found out that the problem was that for some reason, when the x-axis is on time-scale, there's no blank space at the end, but if it's on category there is.
I need it on category as i only want the dates in that i've got data for - is there a way of getting rid of that end blank when x-axis is on category?

Thanks
Grace

 

[PaddyD]

Use the revised formulas for the dynamic named ranges. In the chart, have values set to

=daily!YBuildTarget

and the labels set to

=daily!XDates


seemed to work OK for me...Post back if I'm missing something here!


Paddy

 

[mcgew]

I've used those formulas.
It leaves a blank in the chart if the x axis is category, and doesn't if its time scale. But i need category so that it only displays x values that there's data for.
Any ideas?
Thanks
Grace