Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm using list feature to implement drop-down in excel.

Sheet2!$A$2:$A$101

Is there a way to replace it with something like

Sheet2!$A$2:TILL_THE_END_UNTIL IT SEES A BLANK ROW

instead of hard-coding the last row number.

share|improve this question

2 Answers 2

Using a combination of both COUNTA and INDIRECT formulae:

=INDIRECT("B"&OFFSET($A$2,1,0,COUNTA(A:A),1))

If you had data in cells A2:A5, 5 is your max row. The indirection evaluates to B5. So the formula above will return the value of cell B5.

share|improve this answer
    
I'm confused. Why are you mentioning two columns A and B, when I'm supposed to be concerned with only one column. Is that a typo? –  TPR May 31 '12 at 16:16
    
If cell is blank then you will get wrong output of dropdown. Just verify it. (Lets take an example: First 4 cells has value and next 3 cells are blank then next cell has value? See what happens) –  Siva Charan May 31 '12 at 17:07

I think Tiksi was heading towards using this as a range:

=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!A:A),1)

The COUNTA will essentially return the final row in the sheet, and the offset will set up a range from A2:last-row.

You can add make this a named range to to simplify other formulae.

share|improve this answer
    
If cell is blank then you will get wrong output of dropdown. Just verify it. (Lets take an example: First 4 cells has value and next 3 cells are blank then next cell has value? See what happens) –  Siva Charan May 31 '12 at 17:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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