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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

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
 

Forum statistics

Threads
1,148,269
Messages
5,745,785
Members
423,972
Latest member
franklins

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
Top