Is it possible to have a value inserted by data validation automatically update when the source updates

Question

Basically, on one page I have some fields with a simple data validation "List from a range", which gets its items from some fields on another page. When I update the values on the second page, it does update the validation drop down, but doesn't change the field on the first page.

Is there some way to set it up so that when I choose a value on the first page using the data validation drop down, and then change the value on the second page, it updates on the first?

I assume from what I've been looking up this needs some kind of macro, I was wondering if it was possible to have a dropdown, that when you select a value in the list, it inserts a reference (i.e =A1) instead of the raw value.

Update: Alot of the searching I've done has turned up results for basic data validation usage, And I'm not sure if what I'm asking for even is just validation at this point, but the closest I've found to what I'm looking for is this unanswered question: https://stackoverflow.com/questions/50271882/changing-data-validation-source-values-does-not-update-cells-using-the-original

Update 2: Here is a dummy spreadsheet with an example, and a small explination (as well as a hacky workaround I came up with, which might give you an idea as to what im trying to do)

https://docs.google.com/spreadsheets/d/1s6zhfQuHzR79pwr-P7EHoUSMMl-hdxIuEzf-LQR6XX8/edit?usp=sharing

Answer 1

Okay so, After more searching it really does appear like there is no way to do this without scripting, so after a couple hours of learning how google scripts work I've come up with something that just about works.

It's not the cleanest code i've written, it has some hard coding I don't know how to work around and if the area that was being validated was multiple cells it would have to loop over all of them every time an edit happened, all that being said it does work.

Here is an example of the script working:

and here is the code:

function myFunction(e) {
  Logger.log(e.range.getSheet().getName() + " Has been edited in cell: " + e.range.getColumn() + ":" + e.range.getRow() + ", Old var: " + e.oldValue + " New var: " + e.value);
  var range = e.range;
  var sheet = range.getSheet();
  if (range.getColumn() == 3 && range.getRow() >= 18 && range.getRow() <= 21)
  {
    rankChange(e);
  }

}
function getValue(s,row,column)
{
  return s.getSheetValues(row,column,1,1)[0][0];
}
function rankChange(e)
{
  //if there are multiple area's to track / update, a loop would be needed to check all of them
  var sheet = e.range.getSheet();
  var row = e.range.getRow();
  var newVar = getValue(sheet,row,5);
  Logger.log("Rank Change Detected! " + row + ": " +newVar);

  //this part would go in a loop over all areas of validation
  var oldVar = getValue(sheet,18,6);
  if(oldVar != newVar)
  {
    Logger.log("Updated " + oldVar + " to: " + newVar);
    sheet.getRange(18,6).setValue(newVar);
  }
}

For anyone with the same issue trying to use this, in the sheet go tools > script editor, put this script in, then in the script editor go edit > current projects triggers and you can set up an "on edit" trigger that runs "myFunction", make sure to change stuff like the range it searches and the ranges it updates, someone smarter than me could probably make this just check all cells with validation to see if they need an update dynamically.

Answer 2

Here is a way to do what you want without script, though there are some drawbacks.

When you create the data validation, choose "List of items", then list the cell reference or named range that you want to be automatically updated.

e.g. say B1 use validation of list at A1:A3, then you type in =A1,=A2,=A3

So when you change value at A1:A3, B1 changes as well.

sample sheet: https://docs.google.com/spreadsheets/d/1iNc3dHq7Cbs-iMS54EVaKvCAaIkWcGZHh-yROG_3ymY

The drawbacks are, warnings would always be shown; the dropdown list may be nonsensical so you may want to name your cells; and you cannot change the size of list dynamically.