one approach to make it dynamic is to use named formula/range/cell. Instead of graphing A3:A1000 as your Series 1 Y values, you could assign A3:A1000 to the named range Series_1_Y. Edit your data for the graph and then edit the series and use the named range instead.
You can define names here:
That would be the basic concept of named range, but you need it to be more advanced and to be dynamic. There are a few way to achieve this. The basic thing you are going to need to do is determine the number of entries to include. Or in other words the number of rows. You can do this with the COUNT, COUNTIF, COUNTA and the like. You can then combine those results for use in something like OFFSET or INDEX.
In terms of using OFFSET or INDEX, offset is a volatile function, which means it recalculates everytime ANYTHING on the spreadsheet changes. INDEX on the other hand will only recalculate when when something that affects it changes. So depending on how frequently the formula gets used or the size of the calculation, INDEX won't bog your system down as much.
Since you do not have example data, I will work with the following layout:
You can count you entries in a variety of ways. two options are as follows:
'note the above only works for string version of NA
'and adjust characters to match your spelling
The formula you will want to use in your "Define Name" formula will be something to the effect of:
Repeat the process for your second series changing the reference column
when working with your graph data you will need to have saved your spreadsheet first as your workbook name needs to be a part of the series name. I saved my work book as Dtest.xlsx and you can see the "Define Name" being used as the series below:
Note that if your workbook contains a space in the name surround the name in single quote/apostrophe as follows:
Caveat: you cannot have any other numbers in the column and your numbers will need to be continuous from their starting point.