Dynamic Chart Using Named Ranges

[jjcgirl]

I have a dataset that will be changing on a regular basis, I would like to be able to chart the data using a dynamic named range (the formula below is an example of what I am using to define the named range). Unfortunately when I try to use this named range to create a line graph it is not giving me my desired results... Any help would be appreciated!

=OFFSET('Chart Results'!$A$7,0,0,COUNTA('Chart Results'!$A$7:$A$12),COUNTA('Chart Results'!$A$7:$I$7))

Dynamic Chart Experiment.xlsx

[benishiryo]

hi jjcgirl. you might have to do at least 5 Named Ranges, for each rows. for eg. A would be:
=OFFSET($B$8,0,0,1,COUNT($B$8:$I$8))

is your maximum column in I? i merely amended your formula i saw. if you dont know where it ends, you can use:
=OFFSET($B$8,0,0,1,COUNT($8:$8))

you cant use COUNTA because it will include your formula that returns blank. so change it slightly for Series 2 like this:
=OFFSET($B$8,1,0,1,COUNT($8:$8))

make sure you do like the screenshots i have inside to set the dynamic range. when you right-click the chart & "Select Data", go to the 1st series & "Edit", type in the series name & in the series values, it must contain your sheet name & the Named Range

[jjcgirl]

Benishiryo,

Thanks for the suggestion in my actual spreadsheet I have created a named range for each row, but I would like to be able to vary the number of rows I have without having to create a new named range each time or getting errors from the chart if I have less than 5 rows of data. That's why I was hoping one dynamic named range would do the trick. So I guess the real problem I have is if I delete "E" in cell A12 and the entire row 12 is blank I want it to drop off the chart as if it was never there, but instead the Series 5 line stays on the legend and I get an error saying "A formula in this worksheet contains one or more invalid references."