Charting only Used Range with Named Ranges

All right I have been working on this for awhile and I cannot seem to
get it perfect. I have a number of charts that take daily security
information and chart it as a time series. I am trying to automate
this by using named ranges. When I do this the chart's x axis extends
much further than the data which is charted. Is there a way to use a
countif or sumproduct funtion in the offset formula to only chart the
used data? Any workaround would be appreciated.

Thanks,
Cory


--
cooter24
------------------------------------------------------------------------
cooter24's Profile: http://www.excelforum.com/member.ph...fo&userid=29619
View this thread: http://www.excelforum.com/showthrea...threadid=505416

Read about dynamic charts on my Tips&Tricks page
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"cooter24" <cooter24.2297im_1138299007.2533@excelforum-nospam.com> wrote in
message news:cooter24.2297im_1138299007.2533@excelforum-nospam.com...
>
> All right I have been working on this for awhile and I cannot seem to
> get it perfect. I have a number of charts that take daily security
> information and chart it as a time series. I am trying to automate
> this by using named ranges. When I do this the chart's x axis extends
> much further than the data which is charted. Is there a way to use a
> countif or sumproduct funtion in the offset formula to only chart the
> used data? Any workaround would be appreciated.
>
> Thanks,
> Cory
>
>
> --
> cooter24
> ------------------------------------------------------------------------
> cooter24's Profile:
> http://www.excelforum.com/member.ph...fo&userid=29619
> View this thread: http://www.excelforum.com/showthrea...threadid=505416
>

Thank you I believe I have seen your post. I know how to set up the
named range what I can't figure out is how to get the chart to only
plot the cells that have values and ignore the ones that are blank.

Thanks


--
cooter24
------------------------------------------------------------------------
cooter24's Profile: http://www.excelforum.com/member.ph...fo&userid=29619
View this thread: http://www.excelforum.com/showthrea...threadid=505416

If the filled cells are at the top, and the empty ones at the bottom, you
use an offset statement which includes COUNT to determine the length of the
named range. If you have prepopulated the category labels, use only the
column of data values to determine the length of both ranges. Change
Bernard's definition of Date to the following, so the two ranges are the
same size):

=OFFSET(Sales,0,-1)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

"cooter24" <cooter24.229j3a_1138314001.9702@excelforum-nospam.com> wrote in
message news:cooter24.229j3a_1138314001.9702@excelforum-nospam.com...
>
> Thank you I believe I have seen your post. I know how to set up the
> named range what I can't figure out is how to get the chart to only
> plot the cells that have values and ignore the ones that are blank.
>
> Thanks
>
>
> --
> cooter24