Good day,
I'm using the below for a named range for data validation. This allows user to select from the values in Column A of Sheet1, starting at row 2.
However, its adding a blank selection option at the bottom of the drop down, apparently including 1 row below the last used row in Column A.
Therefore the user is incorrectly allowed to input a blank value.
Is there a way to modify this to only include the used range in Col A (starting at row 2). There are no blank cells mixed in the list (i.e., first blank cell is after the final value in Col A).
I'm using the below for a named range for data validation. This allows user to select from the values in Column A of Sheet1, starting at row 2.
However, its adding a blank selection option at the bottom of the drop down, apparently including 1 row below the last used row in Column A.
Therefore the user is incorrectly allowed to input a blank value.
Is there a way to modify this to only include the used range in Col A (starting at row 2). There are no blank cells mixed in the list (i.e., first blank cell is after the final value in Col A).
Excel Formula:
=OFFSET('SHEET1'!$A$2,0,0,COUNTA('SHEET1'!$A:$A),1)