1

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

Improve this question
4
  • SEQUENCE is not supported by Excel 2021. Commented Nov 2, 2024 at 12:06
  • From Microsoft Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list. Commented Nov 2, 2024 at 12:11
  • @rotabor that is not updated/ also it shows the parent version where the function was actually launched. Refer the original functions list. Also, some older functions may show that works from Excel 2016 onwards, but mostly work from Excel 2010+ or Excel 2007 onwards, MS remove those versions from documentation because Excel 2007 to Excel 2013 are not in support any more. Commented Nov 2, 2024 at 12:16
  • support.microsoft.com/en-us/office/… Commented Nov 2, 2024 at 12:17

2 Answers 2

Reset to default
3

While I'm not sure about the SEQUENCE function and the # operation for Excel 2021, I can propose the next solution:

enter image description here

Data validation formula is =OFFSET($A$1,0,0,MATCH(2,1/($A:$A<>""),1),1)

This formula dynamically tracks the only range in the "A:A" column started from "A1".

UPD It can help somebody with ancient Excel :-).

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Very smart thanks a lot. It's even better to add indrect function to allow people to delete the first row if they want without any trouble. Like this : =OFFSET(INDIRECT("$A$1"),0,0,MATCH(2,1/($A:$A<>""),1),1)
2

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.

Improve this answer

2 Comments

Strange. It contradicts the information following the link provided by me. Anyway, the # operation is for dynamic arrays and shouldn't be supported by Excel 2021, is not?
@rotabor no there is nothing strange. read my comments above. Your link doesn't contradict as well, it only shows where the functions was originally launched, mostly the parent versions. That is supported by Excel 2021, because it uses the # as reference range in DV.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.