Search code examples
excelexcel-formulanamed-ranges

Dynamic Named Ranges - Last Row Number

I have a sheet where a lot of columns are a named range. Their ranges all begin on row 1 and end on row 100. I want to be able to easily change the last row of all the ranges. For example, I would like all the ranges to refer to the value of cell A1 determine what the last row number.

The code currently looks like this

=Sheet1!$D$1:$D$100

If cell A1’s value is 88 then then I want the code to look like this

=Sheet1!$D$1:$D$88

I’m hoping there is a simple formula I can enter to do this.

Thanks.

Solution

  • You can use INDEX function to achieve this. So the edited formula will look as below.

    =Sheet1!$D$1:INDEX($D:$D,$A$1)