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)


  • Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask. Oct 4, 2019 at 4:50
  • 1
    Hi, I hope thats enough Information on my searching, I've not been very successful on filtering out the basic data validation results from what I'm trying to do, I suspect my issue isn't data validation anymore but I'm not sure what else to call it.
    – Hobnob11
    Oct 4, 2019 at 5:22
  • Could you please share a link with some dummy date and the expected results? Oct 4, 2019 at 5:59
  • docs.google.com/spreadsheets/d/… here you go
    – Hobnob11
    Oct 4, 2019 at 7:09

2 Answers 2


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:

Example of 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)

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);

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.

  • I'm not going to mark this question as answered because I was hoping for a non script based solution, so I'll leave it for a bit to see if someone has a cleaner / better solution to mine
    – Hobnob11
    Oct 4, 2019 at 11:27

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.