5

tl;dr: 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 except E5:
=MY_FUNCTION( somehowjoin( E1:E4, E6:E ) )

Background
I'm writing a project tracker in Google Sheets, like Microsoft Project. I have a custom function in Google Sheets 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:

=MAX_LOOKUP( G9, A:A, I:I )

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. enter image description here

So, I'd like to write my formula for each line to skip over the current line, like:

=MAX_LOOKUP( G9, A1:A8 + A10:A, I1:I8 + I10:I )

FWIW my current workaround is to rewrite the custom function's signature like so:

=MAX_LOOKUP_SKIPROW( G9, ROW(), "A", "I" )

and manually construct the two ranges within that function using:

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();

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.

Community
  • 1
  • 1
Phrogz
  • 296,393
  • 112
  • 651
  • 745
  • I'd prefer not to rewrite my custom function to accept multiple arguments, though of course this is a possibility. – Phrogz Feb 19 '16 at 16:39
  • I think a combination of `indirect()` and `row()` could help you construct ranges that exclude the current row. No? – Marc Feb 19 '16 at 16:45
  • @Marc Not as a single argument, as far as I can tell. Am I wrong? Is there a A1 or R1C1 syntax for discontiguous ranges? – Phrogz Feb 19 '16 at 17:35
  • Possibly a duplicate of http://stackoverflow.com/q/18264423/405017 – Phrogz Feb 19 '16 at 17:37

3 Answers3

6

Short answer

Create your own arrays and use them as parameters of your custom functions.

Explanation

In Google Sheets, users could create their own arrays by embracing their elements in brackets.

Instead of

=MY_FUNCTION( somehowjoin( E1:E4, E6:E ) )

use

=MY_FUNCTION({E1:E4;E6:E})

References

Using arrays in Google Sheets - Google docs editors Help

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Thank you! This is exactly what I was hoping would be possible: a simple, built-in syntax that concatenates discontiguous ranges. – Phrogz Feb 21 '16 at 20:22
2

I am not quite understanding exactly what you are trying to do, but this might help. You can join ranges like the following. The first two will return the data to a row or a column. I include these just so you can see what is happening. The third I used in a test to make sure the data could be sent to a custom formula. I hope this helps:

=split(join(",",A2:A6,A8:A12),",",true)

=transpose(split(join(",",A2:A6,A8:A12),",",true))

=MAX_LOOKUP( G9,  split(join(",",A1:A8,A10:A),",",true),split(join(",",I1:I8,I10:I),",",true) )
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
1

When you pass a range to a custom function in Google Sheets, the range comes in as an array of arrays. This is a true array, insofar as Array.isArray() returns true.

Armed with that knowledge, you can create your own custom function:

function UNION(range1,range2){
  return range1.concat(range2);
}

And then simply:

=MY_FUNCTION( UNION( E1:E4, E6:E ) )

...and your original function will have no idea that it is operating on discontiguous ranges that have been merged.

Phrogz
  • 296,393
  • 112
  • 651
  • 745
  • I hold out hope that a more standard way exists for joining ranges, without resorting to a custom function. I'll not be accepting this answer until it becomes obvious that no better choice is possible. – Phrogz Feb 19 '16 at 17:51