Dynamic Dropdown in each cell

[skittlz]

Here's my code:

Sub Macro1()

Asset = 15
'
    With Selection.Validation
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=("Sequence(Asset)")
    End With
End Sub

Unfortunately, I cant get the sequence formula to work in the dropdown - unless I'm doing it wrong.

I'm trying to create dropdowns in a single cell, say B2, from SN001 through SN015 (with the Asset set at 15). In the next cell (B3), the Asset count might change - say 5, so I want the dropdowns to be SN001 through SN005.

I was thinking of using helper cells, but since Asset count will keep changing, it'll change the options in ALL the dropdown. Is there a simple macro way around this? I'm dealing with 35k lines of data, and growing, so Macro is the best way to do it.

How can I do this?

 

[myall_blues]

You can't use dynamic array formulas (such as SEQUENCE) in data validation lists. You can, however, refer to the output of a dynamic array (a spill formula) using the # referencing system.
So for example, you can put =SEQUENCE(15) in G3, and refer to that as the source for your data validation list as =$G$3#
So you can do something like this with helper cells, for example. The helpers can go on another sheet.

As an additional question, is the list the same in all instances but just of varying length?

Book1
	A	B	C	D	E	F	G	H	I	J
1								B2	B3	B4
2			<= Dropdown in B2 refers to H3# as the source					15	5	6
3			<= Dropdown in B3 refers to I3# as the source					1	1	1
4			<= Dropdown in B4 refers to J3# as the source					2	2	2
5								3	3	3
6								4	4	4
7								5	5	5
8								6		6
9								7
10								8
11								9
12								10
13								11
14								12
15								13
16								14
17								15
Sheet1

Cell Formulas
Range	Formula
H3:H17,J3:J8,I3:I7	H3	=SEQUENCE(H2)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=$H$3#
B3	List	=$I$3#
B4	List	=$J$3#