2

I have a spreadsheet formula in which I use exactly the same FILTER function twice inside an INDEX function. Is there any way to avoid repeating myself, and/or a simpler version of the expression?

The expression looks like this (separated into multiple lines for a tiny bit of extra clarity):

 =INDEX(
  FILTER($A$1:$P$1, ($A$1:$P$1 = "ANIM")+($A$1:$P$1 = "COMP")+($A$1:$P$1 = "SENT TO EDIT"),
 NOT (ISBLANK(A2:P2))),
  COLUMNS(
  FILTER($A$1:$P$1, ($A$1:$P$1 = "ANIM")+($A$1:$P$1 = "COMP")+($A$1:$P$1 = "SENT TO EDIT"),
 NOT (ISBLANK(A2:P2)))))`

What the expression does is return the heading of the rightmost non-blank column in the row, so long as the column is one of ANIM, COMP, or SENT TO EDIT.

See the formula in action in column Q of this Google Sheets.

Since the FILTER function is exactly the same both times, if I could use a variable, it could be re-written something like this:

range1 = FILTER(.....); INDEX(range1, COLUMNS(range1));

Is there any way to do something like that - or an equivalent - in order to avoid repeating my FILTER expression?

Community
  • 1
  • 1
Andrew Klaassen
  • 206
  • 1
  • 8

3 Answers3

0

You have the right idea, the way to avoid repeating long subformulas is to move them out of the formula and call the result someway. On certain cases you could use a reference to the range that displays the result but for formulas that returns a dynamic array, a custom function could be a better alternative.

Example:

Let say that you move out 

FILTER($A$1:$P$1, ($A$1:$P$1 = "ANIM")+($A$1:$P$1 = "COMP")+($A$1:$P$1 = "SENT TO EDIT"), 
NOT (ISBLANK(A2:P2)))

to dry!A1 (don't forget to prepend it with =)

The following is just a "mcve". It assumes that te dry sheet only contains the above formula:

function headers(){
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheets().getSheeyByName('dry');
   return sheet.getDataRange().getValues();
}

Replace your original formula for the following:

=INDEX(headers(dry!A1:P1),COLUMNS(headers(dry!A1:P1))

Note: The use of dry!A1:P1 as arguments of headers() is to force it's recalculation every time that the result for the subformula changes.

See also: Is there a way to evaluate a formula that is stored in a cell?

Community
  • 1
  • 1
Rubén
  • 34,714
  • 9
  • 70
  • 166
  • I was hoping to avoid external functions; in this particular case, I think I'd rather repeat myself than use a function. :-) – Andrew Klaassen Nov 25 '16 at 18:54
  • If the resulting array will be always of the same size, you could avoid the custom function. Otherwise, if you don't want to use custom functions, I'm afraid that you should repeat yourself. – Rubén Nov 25 '16 at 19:25
0

You could try something like this:

=REGEXEXTRACT(JOIN("|",FILTER($A$1:$P$1, Regexmatch($A$1:$P$1,"ANIM|SENT TO EDIT|COMP"), NOT (ISBLANK(A2:P2)))),"([^|]+)$")

Using regular expressions is a way to handle arrays like a text.

Max Makhrov
  • 17,309
  • 5
  • 55
  • 81
0

What you are looking for is called a temporary variable, and last I checked, Google sheets doesn't support temporary variables (https://webapps.stackexchange.com/questions/71095/how-to-save-temporary-variables-in-google-sheets-formula). It would be nice if they did!

Alice
  • 1