Formulas `A1:A` and `A1:Z` give invalid name error in Excel?

Selecting a range of definite size (e.g. A1:A2) works just fine, but selecting a range to the last row does not. If relevant, I am using Office 365.

2

2 Answers

You can do open references like A:A or 1:1 but cannot mix and match the fixed and open references. To reference the last non-blank cell:

=INDEX(A:A,COUNTA(A:A))

Or the whole non-blank range:

=A1:INDEX(A:A,COUNTA(A:A))

Change the C1:C to C:C, it will work for you.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy