Need INDEX function to create dynamic range from last row of data upwards a number of rows

I'm looking for a less volatile formula for creating a dynamic range.

Presently I have a need to create a dynamic range to chart data where the dynamic range is the last row in a column with data upwards a number of rows based on a cell value

For example if the data were in column A starting at row 10 and the last cell in column A with data is 100 and the number of rows upwards is 20, I would then require a dynamic range that encompasses A81:A100.

I have used the formula below to look in column D between D39 to D1000 and it returns the correct range even though i may only have data to row 300 say, however it is extremely volatile. cell H34 contains the value for number of rows upwards from the last cell of data.

OFFSET(Sheet1!$D$39,SUM(--(LEN(TRIM(Sheet1!$D$39:$D$1000))>0))-1,0,-MIN(Sheet1!$H$34,SUM(--(LEN(TRIM(Sheet1!$D$39:$D$1000))>0)))+0,-1)

The research I have done shows that an index function would be more stable, but how would one go about creating a dynamic range per my example above.

Thanks for the responses.

I don't think the count function will work as my data that i will be working on starts at row 38 with a column heading and row 39 onwards for actual data.

I did try using something similar to the 2 suggestions but we end up capturing the summary data that is above row 38 which then gives a false range.

Thanks

Try

=SUM(INDEX($A$39:$A$10000,COUNT($A$39:$A$10000)-19):INDEX($A$39:$A$10000,COUNT($A$39:$A$10000)))

Sorry guys none of the solutions appear to be working.

I have attached a workbook where i have data that is charted. The named range "test" contains the latest formula offered by Mr. Topley. When the value in cell B35 is changed the range in then named range "test" ought to change from the last cell in column a back 5 rows so rows 53 thru 49 are charted, when value of B35 = 5.

A second chart i added with name "Offset Formula", this has a named range "test_2" for the chart series which uses the formula is outlined in my initial post, in this case when changing the value of B35 the range is adjusted accordingly in the chart. But as i said earlier the offset function is really volatile and i need a substitute for my named range formula that would be less volatile.

Thanks for your assistance

Try

=SUM(INDEX($A$39:$A$10000,COUNT($A$39:$A$10000)-$B$35+1):INDEX($A$39:$A$10000,COUNT($A$39:$A$10000)))

My apologies to all, I'm just getting back to my project.

The good news is that my problem is solved. I am now able to chart the correct ranges using the suggestion of protonLeah, thank you kindly.

I was also able to use the suggestion from Mr. Topley, this was used to do calculations within my worksheet which replaced a sum calculation using the offset function, again thank you kindly.

Thank you all for your contributions