Ask Your Question

Dropdown reference instead of value

asked 2020-08-01 15:38:31 +0200

Say I have a cell range:


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

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-08-01 17:24:39 +0200

dscheikey gravatar image


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

image description

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.



edit flag offensive delete link more


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.

Z2LUDGFYYXNZ gravatar imageZ2LUDGFYYXNZ ( 2020-08-01 17:49:06 +0200 )edit

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.

dscheikey gravatar imagedscheikey ( 2020-08-01 18:24:19 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-08-01 15:38:31 +0200

Seen: 12 times

Last updated: Aug 01