Reference Cells in Formula move as a new column is added

[GrilledCheese]

Okay, the formula is located in cell DJ12. The formula reads =SUM(DF13:DH13)/SUM(DF12:DH12). I will be adding a new column to the left of the cell DJ12. Okay. So the formula is now located in cell DK12. Now, I want the formula to read =SUM(DG13:DI13)/SUM(DG12:DI12). Any help to automate this formula? I'm stuck.

 

[iliace]

I think the easiest suggestion is to insert a blank column, DJ. Your new column DJ12 will become DK12, and the formula will be =SUM(DF13:DJ13)/SUM(DF12:DJ12). From now on, insert any new column in front of the blank column DJ.

Another option is to define a named range. In your original setup, go to cell DJ12, Define, Name:=cellToLeft, RefersTo:=DI13 (all relative references, no $ signs). So now, you use this formula:

=SUM(DF13:cellToLeft)/SUM(DF12:OFFSET(cellToLeft, -1, 0))

 

[GrilledCheese]

The first suggestion doesn't work. I tried that. In regards to the 2nd suggestion, how do I define a named range?

I think the easiest suggestion is to insert a blank column, DJ. Your new column DJ12 will become DK12, and the formula will be =SUM(DF13:DJ13)/SUM(DF12:DJ12). From now on, insert any new column in front of the blank column DJ.

Another option is to define a named range. In your original setup, go to cell DJ12, Define, Name:=cellToLeft, RefersTo:=DI13 (all relative references, no $ signs). So now, you use this formula:

=SUM(DF13:cellToLeft)/SUM(DF12:OFFSET(cellToLeft, -1, 0))

 

[GrilledCheese]

I know how to define a name now. But what cells or range gets named "cellToLeft? I think we're close to solving this but just a little hiccup.

The first suggestion doesn't work. I tried that. In regards to the 2nd suggestion, how do I define a named range?

 

[iliace]

It's relative to the cell you're currently on.


For example, activate cell B1. Go to Define Name. Make the Refers To A1, and make sure it's fully relative (no $ symbols).

Now, in any other cell, your name cellToLeft will always refer to the cell immediately to the left, regardless of insertion, cut/paste, etc.

 

[mrmmickle1]

Use a named range that extends dynamically based on a specific row:

Named Range = TestRange

=OFFSET(FormulaSheet!9:9, 0, 0, 1,COUNTA(FormulaSheet!9:9)) This formula goes in the Name Manager Range Box. Change 9 to whatever row you are on....

For Example:

Excel 2012
	A	B	C	D
9	1	2	3	4
10	10
FormulaSheet

Cell Formulas
Range	Formula
A10	=SUM(TestRange)

Named Ranges
Name	Refers To	Cells
TestRange	=OFFSET(FormulaSheet!9:9, 0, 0, 1,COUNTA(FormulaSheet!9:9))

If you add extra values to the range like this:

Excel 2012
	A	B	C	D	E	F
9	1	2	3	4	5	6
10	21
FormulaSheet

Cell Formulas
Range	Formula
A10	=SUM(TestRange)

Named Ranges
Name	Refers To	Cells
TestRange	=OFFSET(FormulaSheet!9:9, 0, 0, 1,COUNTA(FormulaSheet!9:9))

The sum will update accordingly. Note: The cells to the right of the range(row) must be blank ("")

 

[GrilledCheese]

I think this option would work great for summing one row but in my case, I want the formula to take the sum of one row and divide it by the sum of the row above it. Also, there are values to the right of the formula.

Use a named range that extends dynamically based on a specific row:

Named Range = TestRange

=OFFSET(FormulaSheet!9:9, 0, 0, 1,COUNTA(FormulaSheet!9:9)) This formula goes in the Name Manager Range Box. Change 9 to whatever row you are on....

For Example:
Excel 2012

	A	B	C	D
9	1	2	3	4
10	10

<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

FormulaSheet​

Worksheet Formulas Cell Formula A10 =SUM(TestRange) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To TestRange =OFFSET(FormulaSheet!9:9, 0, 0, 1,COUNTA(FormulaSheet!9:9)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

If you add extra values to the range like this:
Excel 2012

	A	B	C	D	E	F
9	1	2	3	4	5	6
10	21

<colgroup><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

FormulaSheet​

Worksheet Formulas Cell Formula A10 =SUM(TestRange) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To TestRange =OFFSET(FormulaSheet!9:9, 0, 0, 1,COUNTA(FormulaSheet!9:9)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The sum will update accordingly. Note: The cells to the right of the range(row) must be blank ("")

 

[GrilledCheese]

This is what I'm trying to accomplish. The 3 month formula is showing the % of what was sold to what was purchased. Formula is located in L2 for the 3 month (January, February, and March) and is =sum(H3:J3)/sum(H2:J2). So, next month, I want the 3 month formula to be (February, March, and April).

	January	February	March	April	3month
Purchased	15	20	25		70.00%
Sold	9	18	15

<tbody>
</tbody><colgroup><col><col span="3"><col><col></colgroup>

 

[mrmmickle1]

Use this formula in L2:

=SUM(OFFSET(L2,1,-4),OFFSET(L2,1,-3), OFFSET(L2,1,-2))/SUM((OFFSET(L2,0,-4),OFFSET(L2,0,-3),OFFSET(L2,0,-2) ))

There is probably a simpler way but it gets the job done....