Google Sheet Help

by 5 replies
7
My invoice spreadsheet is always adding more columns. Is there a way to set my formulas to the last column

Code:
=SUM(F40:last40)

instead of =SUM(F40:BY40)
And constantly having to change my final column?

Thanks,

Rick
#programming #google #sheet
  • Try something like =arrayformula(filter(F40, column(F)=E1) - filter(F40, column(F)=E1-1))
  • [DELETED]
  • Try this:

    =SUM( INDIRECT( JOIN(":","F40", ADDRESS(40, COLUMNS(40:40) ,4) ) ) )

    What they mean:
    >> COLUMNS(1:1) will get the number of columns in your sheet (last column)
    >> ADDRESS(row, column ,4) will get the "A1" notation address of the last column in the specified row row
    >> JOIN(":","F40", cellReference ,4) ) will give you string like "F40:BY40"
    >> INDIRECT( JOIN(":","F40", ADDRESS(40, COLUMNS(40:40) ,4) ) ) references the cell range returned by the JOIN

    I know it's a bit complicated but try to understand what each of the formulas do to get what it means.

    Hope this helps!
    Sarf
    • [ 1 ] Thanks
    • [1] reply
    • BRAH!

      That was perfect!
      Thank you so much for taking time to explain the parts of the equation as well!

      Rick


  • Wow I even discovered an error in my travel equation due to not updating the end of the column.

    Would you or someone tell me why the ,4 donates the A1 cell? That one I'm having trouble understanding.

    Rick
    • [1] reply
    • not sure what you are referring to..

      If it's the ADDRESS(row, column ,4) part, it simply means that it will return the 'letter(A) + row(1)' address notation.

      For instance,
      - ADDRESS(2, 5 ,4) will return "E2"
      - ADDRESS(7, 3 ,4) will return "C7"
      - ADDRESS(1, 1 ,4) will return "A1"
      • [ 1 ] Thanks
Join the discussion

Next Topics on Trending Feed

  • 7

    Google Sheet Help

    My invoice spreadsheet is always adding more columns. Is there a way to set my formulas to the last column Code: =SUM(F40:last40) instead of =SUM(F40:BY40) And constantly having to change my final column?