title: Concatenate two ranges in Google Sheets
body: <p><strong>tl;dr</strong>: How do I make two ranges appear as one as an argument to a function? For example, I want to look at every cell in column E <strong>except</strong> E5:<br>
  <code>=MY_FUNCTION( somehowjoin( E1:E4, E6:E ) )</code></p>
  
  <p><strong>Background</strong><br>
  I'm writing a project tracker in Google Sheets, like Microsoft Project. I have a <a href="https://stackoverflow.com/q/35509684/405017">custom function in Google Sheets</a> that allows me to calculate the start date for various tasks by looking at the maximum end date of all tasks that it depends upon:</p>
  
  <pre><code>=MAX_LOOKUP( G9, A:A, I:I )
  </code></pre>
  
  <p>However, the end date for this task is then calculated based on the start date, and that end date is in the column with all end dates that are examined. As a result, I have a circular dependency.
  <a href="https://i.stack.imgur.com/QgOpR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QgOpR.png" alt="enter image description here"></a></p>
  
  <p>So, I'd like to write my formula for each line to skip over the current line, like:</p>
  
  <pre><code>=MAX_LOOKUP( G9, A1:A8 + A10:A, I1:I8 + I10:I )
  </code></pre>
  
  <hr>
  
  <p><em>FWIW my current workaround is to rewrite the custom function's signature like so:</em></p>
  
  <pre><code>=MAX_LOOKUP_SKIPROW( G9, ROW(), "A", "I" )
  </code></pre>
  
  <p><em>and manually construct the two ranges within that function using:</em></p>
  
  <pre class="lang-js prettyprint-override"><code>var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var keys1 = sheet.getRange(keyCol+"1:"+keyCol+(skipRow-1)).getValues();
  var vals1 = sheet.getRange(valCol+"1:"+valCol+(skipRow-1)).getValues();
  
  var keys2 = sheet.getRange(keyCol+(skipRow+1)+":"+keyCol).getValues();
  var vals2 = sheet.getRange(valCol+(skipRow+1)+":"+valCol).getValues();
  </code></pre>
  
  <p><em>This is a functional solution, but makes the function more special purpose. I do not consider this to be an "answer" to this more general question of joining ranges.</em></p>