1

I'm working on an estimate sheet, got it all worked out pretty neatly and trying to tackle the formulae, make them work from the header rows using ArrayFormula instead of having each cell holding formula itself.

reason: when inserting new sections, or 'add new line above' or ... doing any of those actions usually breaks existing ranges and formulas. But when you transpose them to the top and make them work on the entire column it neatly continues working.

I am at the point that adding subsections works well, totals are calculated dynamically, totals per row are also calculated correctly (the formula i'm trying to fix is in the orange cell, I11)

It will check if the cell in column H has the text 'subtotal' and will then:

  • calculate total sum for the row if it isn't
  • calculate subtotal for the section if it is.

I know I can calculate sums using SUM(range) yet to define the range i'd need to know it first.

The formula used is now this:

=ArrayFormula({
  "The New Way";
  IF(
    OFFSET(H11,1,0):OFFSET(H34,-1,0)<>"Subtotal",
    OFFSET(I11,1,0):OFFSET(I34,-1,0),  
    SUM(I14:I16)   // >> this needs to become a dynamic range (from the first empty cell above the active cell to the active cell itself
  )
})

Custom formula As It started dawning on me that I would need appscript here, I tried a custom method to calculate the first empty row above a given cell:

So far that outputs the correct thing as seen in cell B43, but I have not been able to then create a range between that newly generated address and the given one. Especially not as part of the ArrayFormula.

The method is very straightforward:

function firstEmptyCellAbove(cellRef) {
  const sh = SpreadsheetApp.getActive().getActiveSheet(); 
  const cell = sh.getRange(cellRef);
  let previous = cell;
  
  do {
    previous = previous.offset(-1, 0);
  } while (previous.getValue() != "");
    
  return previous.getA1Notation();
}

Please find the working example document here, it is to this document that I refer in the question above.

https://docs.google.com/spreadsheets/d/17FKYkgWXrRCUygT9rugEU7cCOFnEPDm8kpqIBBLLDuo/edit?usp=sharing

  • Hi, @Sander. Have you had any success with developing an solution to your problem? – Tedinoz Jul 6 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.