Named range for data validation incorrectly including blank value

[cart0250]

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).

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

 

[Fluff]

How about

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

 

[cart0250]

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

I did try that, evaluates to error.

 

[Fluff]

It works for me, so can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[cart0250]

It works for me, so can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

I got it working. However, even though it no longer allows user to select a blank from the list (and 'ignore blanks' is unchecked), it still allows blank entries. So I'll have to work that piece out. Anyway, thanks for your help!

 

[Fluff]

Glad to help & thanks for the feedback.