Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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.php...o&userid=29619 View this thread: http://www.excelforum.com/showthread...hreadid=505416 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting only Used Range with Named Ranges
Read about dynamic charts on my Tips&Tricks page
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "cooter24" wrote in message ... 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.php...o&userid=29619 View this thread: http://www.excelforum.com/showthread...hreadid=505416 |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting only Used Range with Named Ranges
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.php...o&userid=29619 View this thread: http://www.excelforum.com/showthread...hreadid=505416 |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Charting only Used Range with Named Ranges
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting unique entries and assigning it to a named range | Excel Discussion (Misc queries) | |||
Named ranges: don't want absolute references | Excel Discussion (Misc queries) | |||
Dynamic Named Range with blank cells | Excel Discussion (Misc queries) | |||
Problem with Dynamic Named Ranges | Excel Worksheet Functions | |||
Named ranges reset. | Excel Worksheet Functions |