Excel – Create Drop-Down List Using a Formula (Without Data Validation)

excel, excel-formula, validation

One can create a list using the data validation tool. However is it possible to do the same using only a formula (not VBA)?

If I have a named range consisting of several cells (Names), I can reference this in another cell (=Names). However only the contents of the first of these cells will appear, and no drop-down menu presenting all options will be created.

Is there a way to do so without VBA and without data validation?


Best Solution

As I know it is not possible to create a drop down list with formula instead of data validation but below method may be used to achieve your goal (Method already also mentioned by @PermaNoob in the comment section.)

  1. A page layout like this:

enter image description here

  1. Data validation formula as following:


enter image description here

  1. And also alert option will be disabled ( to be able to add custom data):

enter image description here

  1. When you write "List" in the cell "A2" you will get the Column C in the drop down list and if you do not write anything in cell "A2" you will get D column in the drop down list.

enter image description here

enter image description here