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.
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.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)
Bookmarks