CODEWITHSUNDEEP
excelexcel-formulavalidation
Nat Aes
Nat Aes

Reputation: 927

Create Drop-Down List Using a Formula (Without Data 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?

Thanks

Upvotes: 1

Views: 14457

Answers (1)

Dubison
Dubison

Reputation: 768

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

Upvotes: 7

Related Questions