Using a formula to determine variable rows in Chart

[Guest]

I am trying to build a standard sheet with a chart. This chart may contain
28, 29, 30 or 31 rows depending upon the number of days in the month the
chart is prepared. A database application loads the data into Sheet1, and
sheet two contains the graph. The graph always shows 31 days. Sheet1
contains a cell stating the number of days to chart but I am, so far,
unsuccessful in using a formula or expression when stating the series range
in the chart data properties. It seems the only expression the chart
understands is a static range of cells. Because of security restrictions on
desktops I am trying to avoid VBA and would prefer a formulaic solution.

 

[Guest]

Hi,

Depends on the version of Excel you are using, let's suppose 2003 - select
the range where the data will appear on the Sheet1, for example A129 (one
row for titles and 28 rows for the minimum number of rows needed for Feb.).
Choose the command Data, List, Create List and click OK.

Now create your chart and select this list range. When you add more data
for 30 and 31 day months the chart wifll automatically expand to chart the
range.

 

[Guest]

Shane,
Using 2007 and not sure how I then connect the series in the chart to the
list. It seems that 2007 will only allow me to specify
"=sheetx!cellrange:sheetx!cellrange". I think your advise appears to be the
solution I seek, now any advise on a 2007 implementation of your suggestion?

 

[Jon Peltier]

You can extend Shane's suggestion and use an Excel 2007 Table to contain the
data. As the table changes its number of rows, any formulas that refer to
all rows of the table adjust to include the new length of the table.

Or you could define dynamic ranges that serve as series data:

http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon

 

[Guest]

Hi Robert,

In 2007 Microsoft has named the command Create Table. Select one row of
titles and the sample data as in my earlier example. Then press Ctrl T the
shortcut for Create Table (same as Create List in 2003 and the old shortcut
Ctrl L also works in 2007). Click OK. Select the data and create a chart,
Excel will handle the rest.

This will become the method of choice for both charting and pivot tables as
time goes by. - Prediction by Shane Devenshire