Dropdown reference instead of value

[Z2LUDGFYYXNZ]

Say I have a cell range:

COLORS
Red
Green
Blue

And I use it in data->validity->cell range to display a selection list, and use it to write some data.

ITEMS | ITEM COLORS
Item1 | Red
Item2 | Green
Item3 | Blue

The problem arises when I want to change the name of one color, say Blue. If I change Blue to Cyan, the values in ITEM COLORS don’t change, although you’d expect for Item3’s Blue to change to Cyan.

I believe this happens because the drop down list simply writes the value of the list item rather than a reference to it.
Is there a way to have a dropdown list write a reference instead of the value, for example =$A$2 instead of Red.

[dscheikey]

Hi,

this is not a big issue. You can create a List with the formulas. Like here:

You can not see the entries in the Dropdown eg Red, Blue ect. But the formula works after you select ist.

Hope that helps.

In case that this answer is what you expect, the please mar it at answers. The little white hook next to my answer.

mfg

Juergen

[Z2LUDGFYYXNZ]

Thank you for the answer but to me this solution defeats the whole purpose of creating a list, which is supposed to simplify the creation of new records while keeping consistency. I wish there were lists with name/value pairs.

[dscheikey]

Hi, the problem is, if you want to change interactively the already selected entry, you have to use a formula or use Search and Replace to change all entries eg. from blue to grey.