CODEWITHSUNDEEP
excelexcel-formulanamed-ranges
Patrick
Patrick

Reputation: 232

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.

Upvotes: 1

Views: 1983

Answers (2)

Harun24hr
Harun24hr

Reputation: 36955

Use OFFSET() function with COUNTA() to make named range till last data entry row. Try-

=OFFSET(Sheet1!$D$1,,,COUNTA(Sheet1!$D:$D))

If you want to take value from A1 cell then you can use-

=INDIRECT("D1:D"&Sheet1!$A$1)

Upvotes: 1

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

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

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

Upvotes: 2

Related Questions