Dropdown list in excel using formula

Question

Is it possible to generate drop-down list using a formula in excel ?

I know it can be done using Data-validation but I don't want to use it.

For example: I have 1, 2, 3, 4 in A1-A4,In cell B1 I will use a formula, which will render a dropdown list with entries containing 1, 2, 3 and 4.

Is it possible ?

Data-validation is the only way to create drop down in Excel. While using the data-validation, either you can use formula directly or using a named range. — Karpak, Oct 29 '16 at 08:52

score 0 · Answer 1 · answered Oct 29 '16 at 08:40

0

I have done quite the same. The idea is to create a NAMED range (that would contain the values you want to show in the list) and use the name of the range in the data validation definition, something like:

Set the value of "Allow" to list,
Set the value of "Source" to =My_Named_Range

That should do. You can also se explicit range definition (e.g. B4:b88) if this range is static.

answered Oct 29 '16 at 08:40

FDavidov

3,505
6
23
59

1

Using data validation is explicitly out of the question. – Axel Richter Oct 29 '16 at 08:44
I can't use `Data Validation` @FDavidov Any other ways other than this ? (Only using formulas) ? – Aerl Oct 29 '16 at 08:52
Sorry, but not that I know of. If I may ask, why is data validation not an option? – FDavidov Oct 29 '16 at 10:01

Dropdown list in excel using formula

1 Answers1