# Another cell reference problem =)

#### Lizardio

##### New Member
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### xenou

##### MrExcel MVP
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

#### goldfish

##### Well-known Member
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

Last edited:

#### xenou

##### MrExcel MVP
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

#### xenou

##### MrExcel MVP
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

#### Lizardio

##### New Member
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.

#### Lizardio

##### New Member
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....

#### Lizardio

##### New Member
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??

#### Lizardio

##### New Member
Hmmm it doesn't really work after all....

#### Gerald Higgins

##### Well-known Member
Hmmm it doesn't really work after all....
Go on, give us a clue . . .

Replies
1
Views
33
Replies
0
Views
27
Replies
9
Views
88
Replies
6
Views
256
Replies
3
Views
39

1,191,684
Messages
5,987,993
Members
440,124
Latest member
dippy_egg

### 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.

### Which adblocker are you using?

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

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