I'm using list feature to implement dropdown 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 hardcoding the last row number.
I'm using list feature to implement dropdown in excel.
Is there a way to replace it with something like
instead of hardcoding the last row number. 


Using a combination of both COUNTA and INDIRECT formulae:
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. 


I think Tiksi was heading towards using this as a range:
The COUNTA will essentially return the final row in the sheet, and the offset will set up a range from A2:lastrow. You can add make this a named range to to simplify other formulae. 

