excelvalidationexcel-formulaexcel-2021

How to use SEQUENCE formula in Excel data validation

I have input data like this:

Start Number:  88
Count:          7

I can then use the SEQUENCE function like this to display a list starting from 88 and iterating for 7

Eg:

=SEQUENCE(C4,1,C3)

Assuming 7 was in cell C4 and 88 was in cell C3

I want to use this list as data validation, so I tried Data... Data Tools menu, in Data Validation I select Custom, then enter the formula =SEQUENCE(C4,1,C3) But I don't get any dropdown.

I also tried enclosing in {} and also tried as List - but doesn't work. Is there any way to do this in Excel.

I am using Excel version 2021

Solution

  • You cannot use an array in Data Validation simply use the cell reference with the # spill :

    enter image description here

    • Formula used in cell B6

    =SEQUENCE(C4,,C3)

    • In Data-Validation use:

    =B6#

    Availability of SEQUENCE() function per MSFT Documentations read here:

    enter image description here

    Dynamic Array Functions don't work with Data Validations. Data-Validations expects a list or a range formula that returns a range, what you need to do is apply the said aforesaid mentioned function in a cell and point the data validation to that cell reference with a spill operator beside it, as shown in the screenshot.