Auto update DV list with dynamic values

[rotorbloke]

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!

[JBeaucaire]

Data Validation - Replace Selection with Formula To allow DV list updating
This is a VBA solution that watches for you to make a selection from a drop down data validation list, then replaces that choice with a formula that resolves to the same value selected. Why? So you can later go to your data validation source list and "edit" it and the edit will flow to all the cells that use that value. So you could edit an item in the list from "dog" to "dogs" and all the other cells that originally selected "dog" would now show "dogs".

Replace Prior DV Choices

There is a sample sheet where you can test this technique out and see it in action.

[rotorbloke]

Thank you JB, I have messed with your code already. The problem is that the list needs to be changed directly in order for your code to work. If the value of "Dog" comes from a cell outside of the list, the choice will not update automatically. Do you have a suggestion as to how the choice can detect a change to the list when it is not directly changed?

Thanks.

[JBeaucaire]

That is not correct. Since my macro replaces DV choices with a FORMULA that is pulling an indexed option from the current source list, regardless of how the source list is generated, by hand or by formulas, the other formulas that reference that list will always show results based on the current list.

So as I understand your question, "How to have cells with a drop DV list update prior choices when the source DV options change?" will absolutely work with the suggested approach.

If you're certain it won't work, that I don't understand, so you'll have to show me in a workbook how my solution fails to work.

[rotorbloke]

Thanks JB, you are absolutely correct, I jumped to a conclusion.
In the first test file attached I have made a simulation and it works as advertised.
Choose from the DV at A1 then change the corresponding row in column C and A1 updates.

In test2 I have managed to duplicate the non updating thing I am experiencing, my brain is not big enough to work out why. Maybe yours is

The DV in A12 chooses from the range G3:G7. Let's choose the first in the list (=G3).
Choose another option from the DV at A3 (which changes G3) and you will see that A12 does not update.
Look at the list in A12 and you will see the value has changed there but A12 retains the old value.

I apologise for jumping to a conclusion and appreciate any ideas you may be able to provide to enlighten me!


test.xlsm
test2.xlsm

[JBeaucaire]

Both your test and test2 files are working the same for me.

[sblakemore]

Thanks for sharing the example files, the Google sites link seems to be broken now.

After a bit of head scratching, I've managed to get this working with DV where the source list is on the same worksheet as the cell with the DV applied.

My question is, is there a way to get this working between sheets?