1

I'm trying to work on a sheet that allows me to switch between a true/false value and based on that result it toggles between 2 different dropdown options in another cell.

Desired Result


  • Cell C2 contains a dropdown of TRUE/FALSE
  • Cells H2/J2/L2 contain a dropdown that is also dependent on the value specified by the C2 dropdown.
  • H2/J2/L2 get the data from 2 separate sheets.
  • Solution scales infinitely based on the corresponding row in the C column IE if it's C3 then the logic needs to apply to H3/J3/L3, etc.

Example


I have the sample spreadsheet that I'm working on here.

  • C2 is pulling the data from the MISC sheet for a boolean value
  • H2/J2 need to pull the data from B10-B27 on either GS256 OR GS256 Classified
  • L2 needs to pull the data from B28-B36 on either GS256 OR GS256 Classified
    • GS256 contains the data if C2 is set to FALSE and GS256 Classified contains the data if C2 is set to TRUE.
  • I want to be able to handle variable amounts of values if for instance more values are added or subtracted on each of the corresponding one.

What I've tried


I've tried to do a mix of to using ArrayFormula with an if statement, however the issue that I face here is that it's dependent on a single cell, and it swaps the data in the multiple places, not just the single cell that I want to impact. So this effectively is just setting the drop downs to a single dataset.

Looking for feedback on how to approach this. All the data I'm getting from an external source via ImportRange. I'm fairly new to using sheets, but understand the basics. If I need to add a script I'm fine doing so since I'm more of a programmer. If this would be better served via a different interface like a web app then I'm fine doing so since this seems like this might be fairly complex with the variability of the data I'm working with on the spreadsheet.

  • @I'-'I I want to be able to account for different data in the same range across different sheets. – Elias Feb 18 '18 at 15:47
  • yeah that data is subject to change, so want to be able to account for that potential change. – Elias Feb 19 '18 at 5:28
1
function dataValidation() {                                                                                     
var ss = SpreadsheetApp.getActiveSpreadsheet();                                                             
var Tr = ss.getRange("'Body Armor'!C1:C20").getValues(); //Testrange
// Variables and sheetnames
        var b = "'Body Armor'!";                                                                        
        var g = "GS256!" ;
        var gC = "'GS256 Classified'!";
        var h = "B10:B27";
        var l = "B28:B36";
// Get DV ranges
        var gh = ss.getRange(g+h) ;                                                                     
        var ghc = ss.getRange(gC+h);
        var gl = ss.getRange(g+l);                                                                      
        var glc = ss.getRange(gC+l);
// Build DV rules
        var ghr = SpreadsheetApp.newDataValidation().requireValueInRange(gh,true);
        var ghcr = SpreadsheetApp.newDataValidation().requireValueInRange(ghc,true);
        var glr = SpreadsheetApp.newDataValidation().requireValueInRange(gl,true);
        var glcr= SpreadsheetApp.newDataValidation().requireValueInRange(glc,true);
// Loop through each cell
        for(i=0;i<Tr.length;i++) {
                var j = i+1;
                var Hr = ss.getRange(b+"H"+j);
                var Jr = ss.getRange(b+"J"+j);
                var Lr =  ss.getRange(b+"L"+j);
// Test the test range and apply corresponding DV
        if (Tr[i][0] == true) {
                Hr.setDataValidation(ghcr);
                Jr.setDataValidation(ghcr);
                Lr.setDataValidation(glcr);
                }
                else {
                Hr.setDataValidation(ghr);
                Jr.setDataValidation(ghr);
                Lr.setDataValidation(glr);
                }
        }
}
  • I'm having issue with this calling when I updated C*. I tried adding an onOpen() and then calling the function, but that doesn't catch the edit event, so I tried making the called function from onOpen be onEdit, which just calls dataValidation. onOpen I'm just adding a menu item to run the script. This is the code that I have added via the Script Editor - pastebin.com/pEuYKPwU. Is there a way to trigger the validation update when C* is edited? IE toggled between TRUE/FALSE. There is a difference between the 2 sheets on B19 in terms of data values. – Elias Feb 19 '18 at 13:49
  • I also tried executing it from =dataValidation() within the sheet itself, however since it's calling setDataValidation() it says permission denied even though the script executes fine and doesn't ask to grant permissions when I change the onOpen to use dataValidation instead. Any thoughts on how to get around that? – Elias Feb 19 '18 at 13:56
  • So I updated the code to be more in-line with what you were saying, but added a e != undefined check because the event doesn't exist unless a edit event has been triggered. I also went into the Edit menu and used the "current project's triggers" option, however when I run the script nothing takes place when an edit event has fired. I made sure to set the onEdit to use the onEdit function, however that's not doing anything. Updated pastebin - pastebin.com/AFakbWcH also I have an onOpen mapped to the onOpen function. – Elias Feb 19 '18 at 21:10
  • Let us continue this discussion in chat. – Elias Feb 20 '18 at 13:42

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.