CODEWITHSUNDEEP
excelvbavalidationdrop-down-menu
phassett
phassett

Reputation: 1

Can the list for an Excel dropdown menu be contained in a single cell?

I have a set of key-value type pairs in excel. For each key-value pair I have a unique list of items that I would like to be the contents of a drop down menu in a separate worksheet. In order to make a drop down menu, excel data validation wants to reference a range of cells to select from.

My question is: Instead of referencing a range of cells for the data validation list, can the data validation list be contained in a single cell corresponding to its key-value pair?

for example:

key | value | [list, of, accepted, values, in, drop-down] |

I suppose I could write a macro to manually check and validate the entered text against the list of accepted values using VBA string comparisons, but this seems tedious and loses the drop down menu capability.

Upvotes: 0

Views: 63

Answers (1)

Max
Max

Reputation: 759

i see two possible ways.

  1. a macro changing the values in the drop-down menu

  2. if there is a limited number of possible entries, you could fill the referecnig-range with formulas, that choose the possible entries. If you change your list to something like

    key&No | value

    X1|valueforX1

    X2|valueforX2

    Y1|valueforY1

    Y2|valueforY2

you can then get the values with a vlookup for "key&No"

Upvotes: 0

Related Questions