Google-sheets – How to find and get data from only a certain, dynamic set of columns in another Sheet

google sheets

I saw a proposed solution here, but it didn't work for me.
How to use arrayformula to find and get data from only a certain, dynamic set of columns?

Using formula:

=SUM(INDIRECT(ADDRESS(4, MATCH("BB", 3:3, 0), 4)&":"&
   SUBSTITUTE(ADDRESS(4, MATCH("BB", 3:3, 0), 4), "4", "")))

I am trying to do something similar but it's not working ("Formula parse error")

Differences from the original (above):

I'm trying to do an average, not a sum.
The data to compare and average are both on another Sheet.

I want to be able to average a column of data below a header from another Sheet,

if the header on that Sheet ('Vends'!) matches the cell to the left on the current Sheet ('AGG-Vends'!).

Eg. If the header on row 2 of Sheet Vends! matches A2 on Sheet 'AGG-Vends'!,

I want to average all the values below that header (row 3 and so on,

with an indeterminate number of values to be added).

My headers on Vends! Sheet are on row 2, so I modified the row number from which to start searching, to row 3.

=AVERAGE(INDIRECT(
         ADDRESS(3, MATCH(A2, Vends!A2:2, 0), 4, TRUE, Vends!)&":"&
         SUBSTITUTE(
         ADDRESS(3, MATCH(A2, Vends!A2:2, 0), 4, TRUE, Vends!),
         "3", "")))

My formula is in B2 on "AGG-Vends'! Sheet (highlighted Blue).
sample Sheet

(Vends may be added or moved around, etc., so I was attempting to circumvent any Sheet-wide catastrophes of having to remodd formulas to fix)

Best Answer

Enter this formula in sheet: AGG-Vends, cell B2:

=AVERAGE(INDIRECT(ADDRESS(3, match(A2,Vends!$2:$2,0), 4,1,"Vends")&":"& SUBSTITUTE(ADDRESS(3, match(A2,Vends!$2:$2,0), 4), 3, "")))

Your formula was close though there are several points of difference:

match(A2,Vends!$2:$2,0): the row reference for the headers must be absolute, otherwise as the formula is copied down the row number will increment.
ADDRESS(3, match(A2,Vends!$2:$2,0), 4,1,"Vends"): The syntax for ADDRESS requires that the sheet should be "text indicating the name of the sheet into which the address points".
- the sheet name should be supplied in double quotation marks, and
- the exclamation point is not required. The function will supply this.
SUBSTITUTE(ADDRESS(3, match(A2,Vends!$2:$2,0), 4), 3, ""): The output for this element is the column letter.
- no sheet name is supplied
- the row number (3) is the value to be searched for. I found that it wasn't necessary to put this in quotes.

Code

function onEdit(e) { 
  var sName = e.source.getActiveSheet().getSheetName();  
  if(sName !== "Logging") {
    var value;
    var mA1 = e.range.getA1Notation().split(":")[0];
    var time = new Date().toTimeString();    
    if(typeof(e.value) == 'string') {
      var aCell = e.source.getRange(mA1);
      value = aCell.getValue();
      var form = "'" + aCell.getFormula();
    } else {
      value = e.value;      
    }
    var data = [sName, mA1, time, value, form];
    e.source.getSheetByName("Logging").appendRow(data);
  }
}

Explained

The onEdit will only work, if the active sheet isn't named Logging. Next is to gather the wanted info and append it to the logger sheet. The range of the active cell needs to be a cell reference (A1). A formula, with other references, will mix up the A1Notation. Therefore the split(":")[0] will take the first active cell reference (if you add a = or +) as range. If the cell happens to have a formula, then the value is obtained through the e.source else through the e.value. The formula is also obtained through the e.source and stringified.

Screenshot

enter image description here

Example

I've created an example file for you: Logger Sheet

Google-sheets – Query to copy from Sheet A to B based on TRUE/false

Use the formula below, in stead.

Formula

=QUERY('A'!A1:I200 , "SELECT * WHERE I = TRUE")