dynamic charts

mcgew

Board Regular
Joined
Sep 4, 2002
Messages
75
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?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Sep 4, 2002
Messages
75
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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Sep 4, 2002
Messages
75

ADVERTISEMENT

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

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
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

Board Regular
Joined
Sep 4, 2002
Messages
75
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,013
Messages
5,569,620
Members
412,283
Latest member
camgree
Top