0

{Z100:100} will select 1 row start from cell Z100 all the way to the right.

{Z100:Z} will select 1 column start from cell Z100 down to the bottom.

What about if i want to refer the most bottom-right cell in simple way? For example select all range starts from cell Z100 until to whatever the bottom right cell is.

{Z100:? }

1
  • I posted a solution in reply to your inquiry two days ago. I see that you are continuing to post new questions but have not responded to the work people have already put in on your behalf on previous posts like this one. Did the solution I posted work for you? I say this as kindly as I can: when site visitors post many questions rapidly as you are doing, but then don't respond when people offer solutions (whether they work or not), it doesn't serve to invite future input from those contributors whose previous work is not acknowledged. – Erik Tyler Jul 15 at 12:49
0

You'd need to use INDIRECT:

=INDIRECT("Z100:"&ROWS(Z:Z))

Understand that INDIRECT references written with string elements like this will not auto-adjust if you add or delete columns. If you want it to auto-adjust with row/column additions/deletions, it will require a bit of finessing:

=INDIRECT(ADDRESS(ROW(Z100),COLUMN(Z100))&":"&ROWS(Z:Z))

Re: ROWS(Z:Z) , the Z:Z portion could be any full-column reference (e.g., A:A). I just tend to like to keep things "of a kind" in my formulas.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.