Excel named range used in chart -

i have defined named range , used feed data in chart in excel. data start row 11 (column b) , may reach until row 500, doesn't expands further row 75. entries #n/a. so, first #n/a, data follows , blank.

the problem data plotted correctly (i.e. data picked , plotted, leaving out #n/a), there times empty chart, if #n/a followed numbers. leave #n/a in, because if replace them blank, chart picks blanks 0 , don't want that.

in column have named range keeps years, used x-axis chart. so, example, dataset looks that: in column row 11 31 have years 1990-2010, in column b row 11 18 have #n/a, , 19 31 numbers. after row 31 there empty cells. let's assume imported correctly in chart. if change data in column #n/a expanding until e.g. b22, empty chart. when check chart , seems picked data until eg b20, empty chart.

i suspect has way defined range, cannot find solution. in advance help.

i found solution problem. problem caused problem in named range definition. happens following. assume have column 5 #n/a on top , 10 values after #n/as (a column 15 cells). when defined named range, excel counts number of cells in column, doesn't take consideration #n/as, counts 10 cells (instead of 15). when try plot series (having used named range dynamic chart), excel starts chart beginning of column, i.e. first #n/a. remembers has counted 10 values series, plots first 5 #n/as , first 5 'true' values, omitting other 5 values.

in case, had following code define named range :

=offset(sheet1!$ap$10,0,0,count(sheet1!$ap:$ap) 

which started line 10 @ column ap.

in order fix problem #n/as on top, have count number of n/as , add them back:

=offset(sheet1!$ap$10,0,0,count(sheet1!$ap:$ap)+countif(sheet1!$ap:$ap,#n/a)) 

and problem solved.