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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,691
Messages
6,143,943
Members
450,516
Latest member
shironokuro

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top