How to copy data validation in a relative way, specifically list from range (with or without scripts) Google Sheets

Question

I have created a spread sheet to track appointments. Within this spread sheet I have dynamic dependent drop down lists, so the choice from list 1 will populate the options in list 2. This is as follows:

Cell C2: Client - Client 1 or Client 2. (this is for the use of the example, the actual lists will expand.)

If cell C2 = Client 1 then the address drop down is as below:

Cell C3: Address - Address 1 or Address 2.

If cell C2 = Client 2 then the address drop down is as below:

Cell C3: Address - Address 3 or Address 4.

The data validation for the client list is simple because this is constant and can be copied from column to column. The data validation for the dependent address list, however, can not be copied across multiple columns as it will always refer to column C and lose connection with the relative client list.

The actual data validation for the cell C3 (Address List) is below:

Cell Range = 'DIARY (V-2.1)'!C3

Criteria = 'DIARY (V-2.1)'!C53:C55

The cells C53:C55 are populated by a filter formula which will sort the relevant addresses dependent on the choice of client in cell C2, this in turn populates the address list in C3.

It may be worth noting that I have 10 sets of these appointment slots per column (10 per day) so I have used a filter function for each of these separate appointments so they work independently, but even this is tedious so my need is to be able to copy the column (or just the data validation) from column C onward and have it stay relative to the filter functions in that column.

Please see the below link to the example spreadsheet and this will all make sense! In the example I have copied column C to column D and the data validation for the address list has detached for all ten appointment slots. (permission is free for all to edit.)

https://docs.google.com/spreadsheets/d/1sOlQEzG1D29RaY86YeR1Da--c8t94J-ZAGjv52U4dsY/edit#gid=1950191921

Note: These cells must be drop down lists - list from range - for the functionality of this specific spreadsheet.

If anyone can help with this it would be MASSIVELY appreciated as I have diligently scoured the forums and cannot find a solution. I did see this video (which is way over my head) that seems to accomplish this using java script:

https://www.youtube.com/watch?v=ZiYnuZ8MwgM&feature=youtu.be

Answer 1

Google Sheets does not currently have a built-in solution for copying/filling data validation references or formulas relatively. But somebody already wrote a nice script in this Google Docs forum post. To avoid just a link as an answer, I'm going to copy in the script and instructions here. Credit to AD:AM from Google Docs forum.

How to use their script:

1. Select a range of cells across which you want to copy a data validation rule, relatively
2. From the Validation+ custom menu, select the appropriate option (all references relative, columns absolute, or rows absolute)
3. The validation of the upper-left cell will be copied to the rest of the range

Link to original solution's example Google Sheets with script already included - you can save your own copy and then start using.

Or to recreate from scratch, here is the script.

function onOpen()
{
  SpreadsheetApp.getActiveSpreadsheet().addMenu
  (
    "Validation+",
    [
      {name: "Copy validation (all relative references)", functionName: "copyValidation"},
      {name: "Copy validation (relative rows, absolute columns)", functionName: "copyValidationColumnsAbsolute"},
      {name: "Copy validation (absolute rows, relative columns)", functionName: "copyValidationRowsAbsolute"}
    ]
  );
}

function copyValidation(rowsAbsolute, columnsAbsolute)
{
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var r = ss.getActiveRange();
  var dv = r.getDataValidations();
  var dvt = dv[0][0].getCriteriaType();
  if (dvt != SpreadsheetApp.DataValidationCriteria.VALUE_IN_RANGE) return;
  var dvv = dv[0][0].getCriteriaValues();
  Logger.log(dvv);
  for (var i = 0; i < dv.length; i++)
  {
    for (var j = i ? 0 : 1; j < dv[0].length; j++)
    {

      dv[i][j] = dv[0][0].copy().withCriteria(dvt, [dvv[0].offset(rowsAbsolute ? 0 : i, columnsAbsolute ? 0 : j), dvv[1]]).build();
    }
  }
  r.setDataValidations(dv);
}

function copyValidationRowsAbsolute()
{
  copyValidation(true, false);
}

function copyValidationColumnsAbsolute()
{
  copyValidation(false, true);
}

Answer 2

If you only need the relative copy for a single sheet, you can produce an ods with OpenOffice or LibreOffice that has a relative validation. This is done by removing the $ signs. If you then open load that sheet to your google drive and allow it to be converted to a google sheet, data validation range will be relative if you copy it anywhere in the same sheet. It is not relative if you copied it to another sheet in the same workbook or other google spreadsheets.

For example, if you make the validation for A7 to be the three cells to the right, you'll have something like follows:

Once you save it without the $ it is relative. Then when you upload it you have a sheet with relative validations.