Reference the last row dynamically in a formula Excel

user_9301989 0
Viewed 84

I want to add the last row in the formula dynamically

I have the formula to copy a range =FullOptionLists!$A$1:$Z$275

To get the last row. I have MAX(ROW(A:Z)*(A:Z<>""))

How to combine them into a single formula FullOptionLists'!$A$1:$Z$+MAX(ROW(A:Z)*(A:Z<>""))

I have also tried & in place of +

Thanks for any assistaance with this

excel-formula excel
2 Answers

I would use INDEX as it is non volatile like INDIRECT():

=FullOptionLists'!$A$1:INDEX(FullOptionLists'!$Z:$Z,MAX(ROW(A:Z)*(A:Z<>"")))

If Z will always end at the bottom of the list this would be quicker.

Numeric in Z:

=FullOptionLists'!$A$1:INDEX(FullOptionLists'!$Z:$Z,MATCH(1E+99,FullOptionLists'!$Z:$Z))

Text in Z:

=FullOptionLists'!$A$1:INDEX(FullOptionLists'!$Z:$Z,MATCH("zzzz",FullOptionLists'!$Z:$Z))
user_48515900

Depending on your data layout, I would just turn your range into a Table and use the table reference. Then you can refer to it merely as

=myTable

Note: A Table will automatically adjust its references as you add/remove columns or rows

user_28729220
Related