Charting and Blank Cells

Posted by Cindy on December 17, 2001 12:47 PM

I would like to create a chart that would chart, for example, A1:B30. A1 through B10 currently contains data. However, the rest of the range is blank. I would like to create the chart for the ENTIRE range but only view the data when that range become populated with data. I would add data every day of the month, but do not want to view blanks in my chart, nor do I want to recreate my chart every day when adding new data. Any ideas???
Have a great holiday if I don't hear from you by then.
Thanks in advance.

Posted by Juan Pablo G. on December 17, 2001 12:54 PM

You have two basic options.

First one, the easy one, hide rows 11:30 and each day "unhide" the next row.

Second one, work with Dynamic Name Ranges, something like this. Go to Insert - Names - Define. Create a name called "Items" and put this in the refers to:
=COUNTA(A:A)

Create another name, "RangeX", and use this formula:
=OFFSET(A1,0,0,Items)
Create another name, called "RangeY" and use this formula
=OFFSET(RangeX,0,1)

Now, select A1:B20, click the chart wizard, and in Step 2, click on "Series". There, in "Values" put
=Sheet1!RangeY
(Replace Sheet1 with the name of your sheet)
and in Axis put
=Sheet1!RangeX

Now, finish customizing your chart, and it will update "automatically" when you put new data...

Juan Pablo G.

Posted by Mark W. on December 17, 2001 12:58 PM

Enter #N/A into cells B11:B30 (nt)