Excel Charting and Blank Cells

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.

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.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

Charting and Blank Cells

Re: Charting and Blank Cells

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