Replace INDIRECT() in data validation rule

Question

I had a working Excel spreadsheet that used indirect() in the data validation and it worked fine. I uploaded it to sheets and converted it, now the indirect does not work.

I have found a link on the support forum that explains it does not work in Chrome but appears to work in Firefox, and the answers and workarounds seem to be for generating a secondary list... which is what I want, but in a data validation across a row.

I have knocked up a simple test sheet, hopefully public and the script editor is visible:

https://docs.google.com/spreadsheets/d/1KUgrdXKIKlk1DWvDOX9cY3B2VnRH_5h_vKuZJlqUlN8/edit?usp=sharing

Hopefully you can see what I'm after. I want the validation in C8 to be the list of items in the category based in B8; C9 based on B9 etc.

EDIT and Update

The question is about a replacement to indirect() in a data validation rule. While I did find a way round this by using indirect(), I preferred the version mentioned by Desire (to whom I have attributed the answer), but I thought I'd document my solution in case the sheet above becomes unavailable, or you cannot access it, or you just wanted a bit more detail.

So, for My Demo I have this:

In A1:C5 are my lists of data with the titles.

In the range B8:B12 I applied a data validation rule of value in range of A1:C1 - this gives the first dropdown.

In Cell E8 I put the formula =transpose(filter($A$2:$C$5, $A$1:$C$1 = B8)) and then copied this down to E12

Finally I put the following in a function and ran it in the script editor.

function runMeOnce() {
    var dst = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange('C8:C12');
    var rules = [];
    for (var i = 8; i < 13; i++) {
      var src = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange("E" + i + ":H" + i);
      var rule = SpreadsheetApp.newDataValidation().requireValueInRange(src).build();
      rules.push(rule);
    }
    dst.setDataValidations(rules);
}

That's all there is, no more onEdit() triggering.

NOTE There is one downside I bumped into with this method though. I have this in place for 6000+ rows in my actual spreadsheet, and across multiple sheets, with some dropdowns having 50-100 items in. This solution seriously eats into the (current) 2 million cell limit.

Hope this helps someone.

Unknown Unknown · Accepted Answer · 2017-10-26T14:56:34

Data Validation rule of the type "List of items" takes only a comma-separated list of values as its parameter, and does not evaluate any formulas you try to put there. It does not matter what the function returns, because it will not be called. If you put, say "=sqrt(A10)" in the field "List of items", that only means that the validation rule will require the string "=sqrt(A10)" to be entered in the cell.

Similarly with "List from a Range". Either what you enter parses as range notation, or it does not. The string "=getValidationRange(B8)" does not parse as range notation, hence the error. The function is never called.

The only type of validation that calls a function is "Custom formula". If you use it, then the validation can be performed as intended: for example,

=match(C8, filter(A2:C5, A1:C1 = B8), 0)

requires the content of C8 to be in the column of the table A2:C5 under the heading that matches the category in B8. However, with a custom formula you do not get a dropdown in a cell.

To get a dynamic dropdown, one can either

Use an auxiliary range

For example, enter filter(A2:C5, A1:C1 = B8) in cell F1, so that the F column is for the categories currently selected. The data validation would be "List from a Range", F1:F. This is a fine workaround for one validation rule, but takes more work when you have multiple ones.

Use a triggered script

Use a script that is triggered on edit and sets data validation rules accordingly; this is discussed in How do you do dynamic / dependent drop downs in Google Sheets? among other places.

Replace INDIRECT() in data validation rule

2 Answers

Use an auxiliary range

Use a triggered script