Create a dropdown list based on formula

[skittlz]

Hi,

I need to create a dropdown list in a cell that will contain the formula =Sequence(10) - so it should have 1 through 10 dropdowns. Eventually my plan is to use this in a Macro, where the number 10 will be a dynamic value - so in each new cell, it'll create a new dropdown list with a different set of numbers. How can I do this? Just typing in the formula in Excel Dropdown list isn't working.

Please advise!

Forgot the tags - Excel, Macro!

 

[kevin9999]

Please try the following on a copy of your workbook. Some assumptions made as indicated in the code.

Option Explicit
Sub New_Validation_Drop_Down()
    Dim ws As Worksheet, a, i As Long, s As String
    Set ws = Worksheets("Sheet1")                   '<<< Change sheet name to suit
    
    'You haven't indicated how you intend the get the 'dynamic value' - here it's hard coded
    i = 10
    
    a = Evaluate("Transpose(Row(1:" & i & "))")     '<<< Assumes your lower boundary is 1
    s = Join(a, ",")
    
    With ws
        With .Range("A1").Validation                '<<< Cell A1 used in this demo - change to suit
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=s
            .IgnoreBlank = True
            .InCellDropdown = True
        End With
    End With
End Sub

 

[Peter_SSs]

Just a comment:
Since the OP is using 365 there is no longer a need to use the somewhat cumbersome Evaluate(Transpose(Row(.. construct.
The required string could be obtained more simply like this

a = Evaluate("Transpose(Row(1:" & i & "))")
s = Join(a, ",")

s = Join(Application.Sequence(, i), ",")

 

[skittlz]

you guys rule!!

1 minor edit - how do i make it in the SN#### format?

 

[Peter_SSs]

how do i make it in the SN#### format?

If you change the "s" line to the following, is that what you mean? If not please clarify (with examples).

s = Evaluate("Textjoin("","",,""SN"" & Sequence(, " & i & "))")

 

[skittlz]

@Peter_SSs - almost! Its populating "SN1" - not going through the sequence. So if there are 10 SNs, its only populating the first one. How can I fix that? Thank you for your help!

 

[Peter_SSs]

I don't think that it should change the result but I have slimmed down the code to this (using 10 SN values and cell A1 on Sheet1 as a demo)

Sub New_Validation_Drop_Down_v2()
  Const i As Long = 10              '<- How many 'SN' values you want
 
  With Sheets("Sheet1").Range("A1").Validation    '<- Adjust sheet name and cell/range address to suit
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Evaluate("Textjoin("","",,""SN"" & Sequence(, " & i & "))")
  End With
End Sub

Here is my result.

1. Is this what you want?
2. Are you saying that you do not get this when you use the code?

 

[skittlz]

thats what I wanted - but I was getting only "SN1" to populate - not the rest. There was an error somewhere - I think I got it. Thank you very much!!

 

[Peter_SSs]

You're welcome. Glad it got sorted in the end.

A word of caution though. There is a bug (in my opinion) in data validation when the list values are just supplied as a string like that.
If a user manually types a value into the cell rather than choosing from the drop-down, it is possible to enter values that are not exactly in the list and this could cause problems if you rely on those values for other formulas etc.
For example, with the sample list you can manually type into A1 say SN8 followed by (and/or preceded by) any number of space characters and Excel will accept it.
In the sample below I typed 3 spaces then SN8 then 8 spaces

skittlz.xlsm
	A	B
1	SN8	14
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LEN(A1)

Cells with Data Validation
Cell	Allow	Criteria
A1	List	SN1,SN2,SN3,SN4,SN5,SN6,SN7,SN8,SN9,SN10

If you wanted to avoid that possibility then it is best to put the list in a worksheet somewhere and use that list in the data validation.
Here Excel will not accept manually-typed values unless they exactly match one of the values in the list.

skittlz.xlsm
	A	B	C
1			SN1
2			SN2
3			SN3
4			SN4
5			SN5
6			SN6
7			SN7
8			SN8
9			SN9
10			SN10
11
Sheet2

Cell Formulas
Range	Formula
C1:C10	C1	="SN"&SEQUENCE(10)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
A1	List	=C1#