Forum Discussion

Colleen_Terry's avatar
Colleen_Terry
Copper Contributor
Apr 19, 2024

Personalized drop-down lists

Hi there! I've been trying for hours to create a auto-fill formula that will allow me to populate a list based on a few rules. Youtube tutorials seem to only talk about simple 1, 2, 3, 4 lists.

 

I need to make a list (column) with the values "1-2 min", "2-3 min", "3-4 min", etc. up to "29-30 min". How can I use the sequence function or drag down to populate this list without having to enter each value separately?

 

Another type of list would be "T01-02-2024", "T02-02-2024", "T03-02-2024", where only the first number in the list changes.

 

Any support is greatly appreciated!!

Formulas and Functions
  • djclements's avatar
    djclements
    Bronze Contributor
    Apr 19, 2024

    Colleen_Terry Hopefully this is what you were going for...

     

    Example 1:

     

    =LET(
    n, SEQUENCE(29),
    n & "-" & n+1 & " min"
)

     

    Example 2:

     

    ="T" & TEXT(SEQUENCE(29,, DATE(2024,2,1)), "dd-mm-yyyy")

     

    Note: if it is your intention to use either of these formulas within Data Validation, they won't work directly as the List Source. However, you can enter the formula in cell A1, for example, then create a Data Validation rule in another cell, where the List Source is simply:

     

    =$A$1#

     

    The spilled range operator (#) will dynamically reference the entire list.

    • Colleen_Terry's avatar
      Colleen_Terry
      Copper Contributor
      Apr 19, 2024

      djclements Thanks for your reply! Unfortunately I have such a basic knowledge of Excel that I don't know what any of this means. I suppose what I should really do is find a beginners online course to get a better grasp of basic functions before I try and hit the more advanced ones.

      Any suggestions on courses you are familiar with, either on Youtube or through an official website, would be great.

      I gotta remember - walk before run!

      Thanks again!

Share

Resources