0

I have a formula in my cells that refers to a separate sheet with some data in a single column, something like

A1:A500

However, new values are being added regularly so 500 becomes 501, 502, etc. I don't want to hardcode the last cell index, nor put there A2000 as a means to account for growth as that creates other problems.

Is it possible to reference the last row dynamically, something like:

A1:LAST(A)

Improve this question

1 Answer 1

Reset to default
0

Yes, my preferred method is INDEX(MATCH())

IF the values are numeric then:

A1:INDEX(A:A,MATCH(1E+99,A:A))

If Text:

A1:INDEX(A:A,MATCH("zzz",A:A))

In both we are using MATCH in a relative search mode, in that it will look for a large number or a string that would be last if alphabetically sorted. Then when it does not find that it returns the last cell that is less than that, which should be the last cell in the column.

Note if the column has both numbers and Text:

A1:INDEX(A:A,MAX(IFERROR(MATCH(1E+99,A:A),1),IFERROR(MATCH("zzz",A:A),1)))
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .