CODEWITHSUNDEEP
excelexcel-formula
Einarr
Einarr

Reputation: 332

Reference the last row dynamically in a formula Excel

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

Upvotes: 1

Views: 1658

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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

Upvotes: 3

Scott Craner
Scott Craner

Reputation: 152450

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

Upvotes: 4

Related Questions