Forum Discussion
Excel drop down lists
I am getting stuck on what the last entry is supposed to be. I think it should look like : =SEQUENCE(30, right here should be column, then this should be the word TODAY for start, then here it is asking for step)
What does it want for column? Location, quantity, or what? Also, what is step that it is asking for? Commas between everything? My brain feels like it is going to explode. lol
Chanda1625 Sometimes Microsoft's documentation is not up to the task, as with the SEQUENCE function. I recommend checking another source, such as this Ablebits article., that gives more information; that article is explicit in identifying the columns and step arguments as optional quantities, each with a default of 1.
Yes, you need to use a comma between each argument. And note that TODAY is not (just a) word, it is the name of a function, and function names are followed by an open parenthesis, etc.
So these formulas are equivalent:
=SEQUENCE(30,,TODAY())
=SEQUENCE(30,1,TODAY())
=SEQUENCE(30,1,TODAY(),1)
=SEQUENCE(30,,TODAY(),1)
I actually need the sequence as a drop down that the user can choose one of the dates from to automatically fill in the cell. I would like it to drop down a list of 30 dates from today forward that can be chosen. If that isn't possible, is there a way to have a calendar pop-up to choose a date from?
Chanda1625 Yes, a dropdown is one of the options for the "data validation" (an Excel technical term, in this case) that Patrick mentioned. I'll presume that you want those dropdowns in column E. So
- Preferably in a separate worksheet, which I will call RefData, use one of those formulas to create your list of thirty values. Do that by storing the formula into, let's say, cell A2 (allowing A1 to be used for a description).
- Format those thirty cells (A2:A31) however you want the dates to appear in the dropdown list. It looks like you want to use a Custom format of "dddd, mmm dd" or "dddd, mmm d" (without the surrounding quote characters), depending on whether or not you want a leading zero on the first nine days of a month.
- Back on your initial worksheet, highlight all the cells in column E for which you want the dropdown to appear.
- In Excel's menus, click Data → Data Tools → Data Validation → Data Validation…; the Data Validation dialog appears:
- On the Settings tab, under "Allow", select "List"; the appearance of that tab changes. Then under "Source", enter "=RefData!A2#" (without the surrounding quote characters).
- You can supply more information to the user using the dialog's Error Alert tab, e.g., mentioning the meaning of those 30 days.
- When everything in the dialog is to your satisfaction, click OK.
You can then see the dropdown when you click in one of those column E cells; click its down arrow to show/hide its list.
FYI, because the TODAY function is volatile, the formula on the RefData that contains it will be recalculated every time the workbook is opened (and every time anything on any open workbook changes, normally). So the list of values will always be updated for the current day.
