Charting only Used Range with Named Ranges

  • Thread starter Thread starter cooter24
  • Start date Start date
C

cooter24

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
 
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
 
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/
_______
 
Back
Top