Dropdown List Based on List Size

[default_name]

Hey guys,

I have a dropdown list that I am trying to create. I have looked at several other places on the internet to try and solve this, but have been unsuccessful.
I've done a run-around with Dyanamic Data, Data Validation and conditional statements and am back to square one.
You guys always seem to give the best feedback and simplified answers.

Here's what's going on.
I have a workbook with two worksheets in it.

Sheet1

	A
1	Versions
2	Red 2.0
3	Red 2.2
4	Blue 1.6
5	Blue 1.8
6	Violet 3.4
7	Green 3.4
8	Blue 1.8a
9
10
11
12

<tbody>
</tbody>

Sheet2

	A	B
1	Which version are you using?	*dropdown list is here*

<tbody>
</tbody>

In Worksheet 2 I have Data Validation set up in cell B1.

='Sheet1'!$A$2:$A$20

I will potentially have more items to add to the list in Sheet1 in the future (hence the range going to A20).

Since the range I selected goes to A20, there are 12 blank spots that appear at the bottom of my Sheet2 dropdown list.
I check-marked 'Ignore Blank' in the Data Validation window with the hope that the dropdown list will only show the current options in cells A2:A8.
The 'Ignore Blank' doesn't seem to be ignoring those 12 blank cells.

What am I doing wrong here? Is there another method I could use?
Is there a way to set up Data Validation to auto-adjust for the Sheet1 list size?

Thanks in advance for your help!

 

[jmacleary]

Hi there.

As far as I know there is no formula way of doing it, but providing you can use macros, the following small routine will do the trick for you. If you put it in a workbook open routine, or you could run it in a change event handler on Sheet1:

Sub ReSizer()

Dim LastRow As Long
    LastRow = Sheets("Sheet1").Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

    With Range("B1").Validation
        .Modify Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$A$1:$A$" & LastRow
    End With
End Sub

 

[Fluff]

If you turn your list into a table and then select A2:A8 & give it a name using the name manger, you can then refer to that name in the Data validation options.
Whenever you enter a new value at the bottom of the list, it will be automatically included in the DV

 

[Eric W]

Try using this as your DV formula:

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

assuming no gaps in your list.

 

[default_name]

You guys are wizards!!
Thanks!

 

[Fluff]

Glad we could help & thanks for the feedback