Aug 17 2023 01:53 PM
Hello-
How do I create a formula for sequential dates in a drop down list?
For example: I would like to be able to click the drop-down to pick Thursday, August 17, Friday, August 18, Saturday, August 19, etc.
I thought it would look something like this but, I can't seem to make it work:
=TODAY()+1, =TODAY()+2, =TODAY()+3, and so on.
Also, is there a way to enter a job number from our service software in the spreadsheet and have it pull the specific service address into a certain cell in the same spreadsheet?
I am trying to use the spreadsheet as a template in an email for our technicians to fill out cohesively. My goal is to have the spreadsheet set up with specific drop downs to choose from and then have it automatically be sent to a select group of recipients.
Any help would be appreciated!
Aug 17 2023 02:06 PM - edited Aug 17 2023 02:07 PM
The data validation won't accept a dynamic array but you can refer to a spill in the sheet containing the dates.
This formula produces an array of 30 dates starting from today:
=SEQUENCE(30,,TODAY())
Place it somewhere in the sheet. Then in data validation, refer to the spill:
Aug 18 2023 01:43 PM
Aug 18 2023 08:15 PM
Aug 19 2023 12:17 AM
@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)
Aug 19 2023 01:48 AM
You can use function arguments to aid in construction of the formula.
If you enter =SEQUENCE( then press Shift+F3 (or click the Fx button) then you'll get this menu:
Aug 24 2023 06:21 AM
Aug 24 2023 06:22 AM
Aug 24 2023 06:24 AM
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?
Aug 24 2023 06:24 AM
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?
Aug 25 2023 09:28 AM
@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
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.