I have a simple spreadsheet about musical notes that does the following:
First, a user chooses whether to display sharps or flats by choosing this from a drop-down menu. This triggers other cells in the spreadsheet to display the chromatic scale in either sharps or flats. See below.
This works fine. Then another cell uses data validation for a drop-down menu where one of these notes can be chosen. See below.
This works fine as well except that cell D2 does not automatically update to the corresponding cell in the data validation list if cell B2 is changed. In other words, if cell B2 is changed, it is possible that cell D2 contains text that does not appear in the list of notes. See below for a visualization of this.
In the image above, cell B2 was changed to "Sharps" which correctly updated the list of notes. However, since cell D2 had already chosen a flat note (in this case, Db), cell D2 did not automatically update to its corresponding sharp note (in this case, C#).
All that said, my question is: Is it possible to have cell D2 automatically update to its corresponding value in the data validation list of notes, if the data validation list changes? (preferably without the use of VBA)
