7 {{ upvoteCount | shortNum }}
7 {{ upvoteCount | shortNum }}

Google Sheet Help

by hometutor
5 replies
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
#google #sheet
Avatar of Unregistered
  • Profile picture of the author whitehat
    Try something like =arrayformula(filter(F40, column(F)=E1) - filter(F40, column(F)=E1-1))
    Signature
    BCA Social Profile
    {{ DiscussionBoard.errors[11358826].message }}
  • Profile picture of the author Sarfaraz Karmali
    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
    {{ DiscussionBoard.errors[11358834].message }}
    • Profile picture of the author hometutor
      BRAH!

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

      Rick


      Originally Posted by Sarfaraz Karmali View Post

      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
      Signature
      Video - How Local Businesses Benefit from Monetized On-Topic Breaking News Releases Linking Back to Their Websites
      {{ DiscussionBoard.errors[11360805].message }}
  • Profile picture of the author hometutor
    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
    Signature
    Video - How Local Businesses Benefit from Monetized On-Topic Breaking News Releases Linking Back to Their Websites
    {{ DiscussionBoard.errors[11360809].message }}
    • Profile picture of the author Sarfaraz Karmali
      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"
      {{ DiscussionBoard.errors[11361385].message }}
Avatar of Unregistered

Trending Topics