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
You cannot use an array in Data Validation
simply use the cell reference with the #
spill :
• Formula used in cell B6
=SEQUENCE(C4,,C3)
• In Data-Validation use:
=B6#
Availability of SEQUENCE()
function per MSFT Documentations read 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.