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?
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.