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)

And constantly having to change my final column?

Thanks,

Rick
• Try something like =arrayformula(filter(F40, column(F)=E1) - filter(F40, column(F)=E1-1))
Signature
{{ DiscussionBoard.errors[11358826].message }}
• 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 }}
• 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 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[11360805].message }}
• 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
{{ DiscussionBoard.errors[11360809].message }}
• 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 }}

WF- Enzo

• ### The mental buy button

WF- Enzo

Ahh the buy button. That one requirement whenever someone wants to post a WSO. That final hurdle to finally getting the latest GPU you just bought online. But there's more ... [read more]

• ### Personalised marketing

WF- Enzo

Watch this TED Talk as Nicole Martin, owner of SEOcial Consulting breaks down the myths around social media marketing. Bridging the gap: personalised marketing

• ### Survival niche has infoproducts?

camcarter

Hi all - I'm considering getting into the survival niche but I prefer infoproducts over physical products... obviously there's a lot of physical products to sell in this niche.. just ... [read more]

• ### New Bing Webmaster Tools Live With New URL Inspection Tool Jul 30, 2020

Jeffery in SEO

New Bing Webmaster Tools Live With New URL Inspection Tool Jul 30, 2020 by Barry Schwartz Search Engine Roundtable Excerpt: In February Bing announced it was working on a new ... [read more]