title: How to fix "Loading..." error in multi-row, dynamic, dependent drop-down list in google sheets?
body: <p>I want to reduce human error &amp; increase staff productivity by implementing multi-row, dynamic, dependent drop-down in google sheets. Check <a href="https://docs.google.com/spreadsheets/d/1GLqvHq14PerkD4wtjMRaNZ4h1V6VW8M5tdeGjxPNKMg/edit?usp=sharing" rel="nofollow noreferrer">sample sheet here</a>.</p>
  
  <p>This is for a transport company. We receive orders daily that need to be tracked.</p>
  
  <hr>
  
  <p>In column B in Orders sheet ("Consignor"), data validation is enforced by a simple drop-down populated from column A in Consignee sheet.</p>
  
  <p>I want to enable my staff to make new entries (those not available in drop-down) in column B (orders sheet) itself, and from the subsequent rows, the newly added entry should be an option in the drop-down. </p>
  
  <blockquote>
    <p>To that end, I entered the following formula in cell A2 in Consignees sheet, and it works: <code>SORT(UNIQUE(Orders!B2:B),1,TRUE)</code>.</p>
  </blockquote>
  
  <hr>
  
  <p>Now, in Orders sheet, I want a drop-down in column C based on option selected in column B. And similar to column B, I also want to enable the staff make new entries (those not available in drop-down) in column C itself, and from the subsequent rows, the newly added entry should be an option in the drop-down. </p>
  
  <blockquote>
    <p>To achieve that, I did the following:</p>
    
    <ol>
    <li><p>Entered this formula in cell B1 in Consignees sheet:
    <code>TRANSPOSE(A2:A)</code>.</p></li>
    <li><p>Entered this formula in cell B2 in Consignees
    sheet: 
    <code>SORT(FILTER(Orders!$C$2:$C,Orders!$B$2:$B=B1),1,TRUE)</code>.  </p></li>
    <li><p>Copied and pasted cell B2 horizontally to cell Z2.</p></li>
    </ol>
  </blockquote>
  
  <p>This achieves the objectives of creating the data for dependent drop-down, and allowing the staff to make new entries simultaneously.</p>
  
  <hr>
  
  <p>We have a lot of existing data that I want to paste (>2000 rows) &amp; a lot more will be added (>50 rows) daily.</p>
  
  <p>I tried using array formula with Indirect function, but it is tedious, limited &amp; slows the sheets considerably.</p>
  
  <hr>
  
  <p>So, I realized we need google apps script. But I am not a coder and tried learning the script of creating dynamic drop-down from <a href="http://www.chicagocomputerclasses.com/google-sheets-apps-script-dynamic-dependent-dropdown-data-validation-lists/" rel="nofollow noreferrer">this link</a>.</p>
  
  <p>It's an amazing tutorial &amp; I copied and tried customizing the code for my purpose. Here's the code:</p>
  
  <pre><code>function onEdit() {
    var tabLists = "Consignees";
    var tabValidation = "Orders";
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var datass = 
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  
    var activeCell = ss.getActiveCell();
  
    if(activeCell.getColumn() == 2 &amp;&amp; activeCell.getRow() &gt; 1 &amp;&amp; ss.getSheetName() == tabValidation) {
  
      activeCell.offset(0, 1).clearContent().clearDataValidations();
  
      var consignor = datass.getRange(1, 2, 1, datass.getLastColumn()).getValues();
  
      var makeIndex = consignor[0].indexOf(activeCell.getValue()) + 2;
  
      if(makeIndex != 0) {
        var validationRange = datass.getRange(2, makeIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
      }
    }
  }
  </code></pre>
  
  <p><strong>The problem is that the drop-down sign that comes in column C once I select something in column B shows "Loading...". It doesn't show the options.</strong></p>
  
  <hr>
  
  <p>I know there's some issue with my customization of code, but I can't figure it out since I don't know coding.</p>
  
  <p>I also want to do similar things in columns E &amp; F in orders sheet and have created Destinations sheet for the same.</p>
  
  <p>Please let me know the solution &amp; how can I expand it to columns E &amp; F too.</p>
  
  <p>Thanks a lot!</p>