Another cell reference problem =)

[Lizardio]

I have 5 columns set up: A,B,C,D,E
D is the sum of A and B
E is the sum of A,B,and C

As I add in a new column to the right of C (call it C2), I need D (which has shifted over one) to sum A,B, and C.

I also need E (which has also shifted over one) to sum A,B,C, and C2.

Essentially I need a function in a cell that will be able to reference two cells to the left even as more cells are added.

Sorry if this is totally confusing....
<!-- / message -->

 

[xenou]

You may want to just add all the months up front and fill in the columns as the year progresses. This is also a good case where you could hide the months that aren't being used, and unhide one more each month.

AB

 

[Lizardio]

hmm yes well I suppose a huge IF string could be used.... but that doesnt seem worth the initial work. Thanks so much for your help!!

 

[xenou]

You caught me napping.

Here's a try:

book2
	A	B	C	D	E	F	G
1		Jan	Feb	Mar		Prev	Curr
2	Mt.Polley	6,074	4,447	1		10,521	10,522
3
4
5
6
Sheet1

Formula in cell F2 is:
=SUM(B2:E2)-D2

Formula in cell G2 is:
=SUM(B2:E2)

Here I am using the empty helper column in column E so you would add new columns to the right of E. E can be hidden or reduced to a narrow width (my preference).

 

[xenou]

Goodness...
that doesn't work either.

Okay, in F2:
=SUM(B2:E2)-OFFSET(F2,0,-2)

Other one is okay!

 

[xenou]

And (did my ego suffer here?) this works but I think we'd started in the wrong column.

book2
	A	B	C	D	E	F
1		Jan	Feb	Mar	Prev	Curr
2	Mt.Polley	6,074	4,447	20	10,521	10,541
3
4
5
6
Sheet1

Formula in Cell E2 is:
=SUM(OFFSET(B2,0,0,1,COLUMN()-3))

Formula in Cell F2 is:
=SUM(OFFSET(B2,0,0,1,COLUMN()-3))

No help column here.

 

[Lizardio]

Oh my god that is amazing!! Thank you so much!!!

 

[xenou]

Welcome!

Keep Excelling.