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

0
votes

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?

Thanks

1
excelexcel-formulavalidation
No, there isn't. What are you trying to achieve? Why no data validation? – Jean-François Corbett
I'm trying to dynamically change the data validation type of a cell (i.e. switch between free text, or a list, or a date etc) without VBA. I can't seem to do so using the data validation tool itself as one has to select only one of the allowed data types, so have tried doing so using a regular formula. – Nat Aes
Have you tried putting a formula in as your data validation source? You can use the formula to switch between a list of items or a list of dates. Then, just turn off the Error Alert to allow the user to type freely. This would allow your users to accomplish all of those. – PermaNoob

1 Answers

3
votes

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:

    =IF($A$2="List",$C$2:$C$8,$D$2:$D$8)

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