I have a Data Validation list whose options are derived from other cells and wish to have the value of the selection update automatically upon change. Let's say I have a DV list at A1 which references a list B1:B3. The values in B1:B3 are derived from the values at C1:C3 (B1 =C1 etc.). Changing a value in C3 will update the DV list but not the flat value in A1, it has to be selected manually. I can use VBA to get the value auto updated with a direct change in B1:B3 but not with a change at C1:C3, the change is not detected.

Any thoughts?
Thanks!