Ask Your Question

Auto-update selected data if validity cell range content changes [closed]

asked 2012-05-24 17:21:55 +0100

anonymous user


Hi. In calc, I have set the cell B1 to a dropdown list with the contents of a valid cell range $A$1:$A$2 (Data > Validity).

Say I have selected "001" (value of A1) for B1. When I go to cell A1 and change its value "001" to "X001", the value of the cell B1 doesn't change automatically. I would have to reselect the right value manually, this time "X001".

Is there a way to get a validity dropdown list and have the value of B1 automatically updated if the value of A1 is changed?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-16 04:24:52.673872

1 Answer

Sort by » oldest newest most voted

answered 2012-05-24 17:44:09 +0100

mahfiaz gravatar image

To keep it short, no.

If this is really important to you, you could write a Basic script to do it for you, but it would need a set of previous data held somewhere, so it would know what are the correct replacements.

I would suggest another method, don't know if it is useful. Use indexes, so let's say you pick 1 from a dropdown. Then INDEX, OFFSET or VLOOKUP fills a cell next to the number, and if you update your table of valid answers you would always get the n-th value, just as you wanted. The downside is, you would have to look for the values from the table. But if it is small enough to be placed next to your magic cell, then it might be even easier to use.

And if this seems stupid, you could go an extra mile and use a column next to the valid values, where the first 1 or yes would mean that you picked this exact value. Any later ones or yesses might go ignored.

edit flag offensive delete link more

Question Tools


Asked: 2012-05-24 17:21:55 +0100

Seen: 1,491 times

Last updated: May 24 '12