Hot answers tagged


Is it at all possible to use column headers like this? Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ADDRESS to get the cell reference. Finally, you need to use SUBSTITUTE to remove the row number from the cell reference. =QUERY(Sheet1!A1:C,"SELECT A, B, C where ...


The main goal is to make sum ArrayFormula. The original brilliant solution was provided here. And your formula is: =ArrayFormula( IF($Q2:Q="No", SUMIF(IF(COLUMN(J2:P2),ROW(A2:A)),ROW(A2:A),J2:P), IF($Q2:Q="Yes", SUMIF(IF(COLUMN(R2:X2),ROW(A2:A)),ROW(A2:A),R2:X), IF($Q2:Q="",""))))


The way I usually tend to do my arrayformulas, and actually any formula that requires action based on a value in another cell, is i start them off with an ISTEXT statement, and then just a simple comma in place of the else portion: ={"Start Time";arrayformula(IF(ISTEXT(B2:B),text(C2:C+vlookup(D2:D,Administrative!$A$1:$C$10,2,false),"mm-dd-yyyy ...

Only top voted, non community-wiki answers of a minimum length are eligible