1

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

  • Welcome. Please add a brief description of your search/research efforts as is suggested on How to Ask. – Rubén Oct 4 at 4:50
  • 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 at 5:22
  • Could you please share a link with some dummy date and the expected results? – marikamitsos Oct 4 at 5:59
  • docs.google.com/spreadsheets/d/… here you go – Hobnob11 Oct 4 at 7:09
0

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)
  {
    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.

  • 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 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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