Another cell reference problem =)

Lizardio

New Member
Joined
Jun 11, 2008
Messages
8
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 -->
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
This seems to work:

In cell D1:
=SUM(OFFSET(A1,0,0,1,COLUMN()-2))

In Cell E1 (same Formula):
=SUM(OFFSET(A1,0,0,1,COLUMN()-2))

AB
 
Upvote 0
This code will take A(whatever row you are in) and increase it to be the number of colums as Column(E1)-2. Just make sure to point E1 to either 1 column below your current cell, or one column above to prevent circular reference. Place this in Cell E1:
Code:
=SUM(OFFSET($A1,0,0,1,Column(E2)-2))

Hope this helps,
~Gold Fish

Edit, Alex beat me to a slightly better version :biggrin:
 
Last edited:
Upvote 0
another way would be to add a helper column:

Put an empty column "C2" between your columns and the sums - when you add in a new column, add it in to the left of this empty column. Be sure your sums include the empty column...

This way you add columns inside the sum range, not to the right of it, and the formulas will adjust automatically.

AB
 
Upvote 0
Edit, Alex beat me to a slightly better version

I was expecting to be too late myself... ;)
Still wondering if there's another alternative ...

AB
 
Upvote 0
Hello! Thank you for taking interest in my problem! Unfortunately I don't understand. I looked up the function "offset" in excel but couldn't wrap my head around that either....

Here is a sample of what I am trying to do!

<TABLE style="WIDTH: 697pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=927 border=0 x:str><COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" span=3 width=142><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" span=3 width=112><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 38.25pt" height=51><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=142 height=51> </TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=142>JANUARY HOURS</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=142>FEBURARY HOURS</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>MARCH HOURS</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>APRIL HOURS</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 84pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=112>MAY HOURS</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 76pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=101>PREVIOUS YTD TOTAL</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>YTD TOTAL HRS</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Mt. Polley</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="6074">6,074</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num="4447">4,447</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" x:num>0</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">?</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">?</TD></TR></TBODY></TABLE>

I want PREVIOUS YTD TOTAL to be the sum of Jan hours, feb hours, march hours and april hours.
I want YTD TOTAL HOURS to be the sum of jan hours, feb hours, march hours, april hours, and may hours.

At the end of this month I will want to add June, therefore PREVIOUS YTD TOTAL must have may added to it and YTD TOTAL HOURS will have june added to it. its rows 1 and 2 and columns 1,2,3,4,5,6,7,8.

Thanks so much...I really appreciate it.
 
Upvote 0
Oh and I also considered the "helper" column before but it wouldn't quite work... it would be best to reference backwards instead of forwards....
 
Upvote 0
Actually Alex... your first help post worked after I tweeked it to refer to the cells I wanted... although now E includes D in the sum... how to I fix that??
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top