"Select" Data Types for Choosing Different Formulas

I’m not sure if you intended Select data types to work this way or not, but I discovered that you could use a variety of formulas or dates as the select options, and the formulas actually evaluate! This could have some interesting uses, and I’m only just barely thinking through the implications.

Look at just the following basic example:


You could choose to use the TODAY() formula, or reference cell $J$3 or add 3 days to the date in cell A2 or use the date 5/1/2020. If you manually enter a date, that is automatically added to the select list.

The point is that you actually have a drop down that lets you choose different formulas to use in that cell. That is really cool (and would work even better if the drop-down arrow continued to show up after choosing an option).

In Excel and Google Sheets you either enter a value or a formula. You can create a drop-down list with data validation, but you can’t use formulas in the list. So, what you have done is very interesting.

This would be even more interesting if you could use this within a table with working relative references (so that if you chose the formula, the references could reference values in the current row, or maybe a previous row).

The application where I was trying this out was in a gantt chart where you may want to (1) define the start date as a lookup function (to start the next day after the specified task ID), (2) use WORKDAY to start the next workday after another task’s end date, (3) choose the Project Start Date cell, (4) add a day to the previous task’s end date or (5) enter a date manually.

Another more general application of this technique is to use it as a way of preserving the original formula while allowing the user to override the input with a manual number. For example, you might want to allow an invoice number to be entered manually, or selected as the next sequential number from a column of invoice numbers:


Although this is already working, my suggestions for making this a feature rather than a convenient accident would be to make the drop-down arrow still function if you’ve chosen the formula, and to make relative references function correctly within a table as rows are moved or as the formula is copied down.