Can I have a dropdown list in cells that update automatically when the members of the list change

[someone_somewhere]

So, I have some cells that are data validated. The dropdown list, however, will be one of two lists, which change dynamically (it’s a range at the moment.). Alas, whenever the list changes, the old values of the list are left in place.

As you can see here, the cell has a value from one list, but another list available in the drop down.

Is there a way to get the cell to update automatically? Any method is acceptable.

I’d rather avoid macros, but I have basic programming skills if there is no other way.

[Lupp]

The Cell Range variant of >Data>Validity>>Criteria accepts calculated range references. You can use OFFSET() or INDIRECT().
As always the entries once chosen are not afflicted/changed if after the choice something is changed ina list or a range.
So I don’t understand what you actually mean by “automatically”.

There are means ( not directly by Data>Validity, however) to get an ordinal number (index) into a list when choosing. If you then use the index to get the actual entry, you can this part of the job automatically adapt to a changed range, I don’t see a reasonable use-case.

This is not about the view. You should therefore not attach an image, but an example file (,ods in thus case).

[schiavinatto]

Veja este exemplo, se é o que deseja…
See this example if that’s what you want…

someone_somewhere.ods (11.1 KB)