Automatic Chart Update when Cells are inserted in rows above previous data

**Porkytails** · 03-31-2013, 11:06 PM

Hello
I am trying to create a named range using the offset function to automatically update values for my chart. I have seen many, many solutions when the new data appears below the previous data set, but none when the newest data appears at the top, below the title. For example, when you have the name "Date" in A1 and ascending dates from A2:A10, and you add new consecutive ascending dates from A11:A20, your formula in the "refers to" section of the Named Range is:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A2:$A30)-1)

However, my dates are descending and I insert new dates between the title Date in A1 and the previous data below. For example if my last date is 3/31/13, currently in cell A2, I want to insert a new row in A2 for 4/1/13. I want to update my chart automatically. I have tried several formulas, none of which have worked. Here is one:

=OFFSET(Sheet1!$A$2,COUNTBLANK(Sheet1!$A$2:$A$20),0,20,0)

Lost. Any help would be appreciated. TIA.

**dilipandey** · 04-02-2013, 07:51 AM

Hi Porkytails,

Upload a sample workbook and mention what range you need to capture using defined name. Thanks.

Regards,
DILIPandey
<click on below * if this helps>

**Porkytails** · 04-29-2013, 04:13 PM

I figured it out. All you need to do is to include $A$1 in the series and Row 1 of every series you intend to chart, and excel will automatically skip all the blank cells in between the title in Row 1 and the beginning of the data, for example 10 rows down. If you add a row of data, say, 9 rows down, the chart will automatically update itself.