title: How to sort only those rows which have no blank cell?
body: <p>I have a Google Spreadsheet with two separate sheets. The first one is just a big list of names and data, and the second is supposed to be a sorted listing of all the data on the first sheet (sorted by, say, last name). Here is the current way I am defining the second sheet:</p>
  
  <blockquote>
    <p>=sort(sheet1!A2:L100, sheet1!D2:D100, TRUE)</p>
  </blockquote>
  
  <p>Which works fine for the most part, except for one issue: in sheet1, some of the cells in 4th column (column D) are blank. How can I change the formula so that the sorting ignores such rows which has a blank cell in column D?</p>
  
  <p>The formulas i tried but got undesirable results : </p>
  
  <ol>
  <li><blockquote>
    <p>=arrayformula(if(istext(sheet1!D2:D100), sort(sheet1!A2:L100, sheet1!D2:D100, true), ""))</p>
  </blockquote>
  
  <p>It sorted as desired but with one issue - blank cells were not pushed at the end but scattered in between the rows.</p></li>
  <li><blockquote>
    <p>=arrayformula(sort(filter(sheet1!A2:L100, istext(sheet1!D2:D100)),sheet1!D2:D100, true))</p>
  </blockquote>
  
  <p>Though the filter part does its job perfectly but when coupled with sort, it is giving an error : Mismatched range lengths.</p></li>
  </ol>