Reference the last row dynamically in a formula Excel-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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