Referencing (cell - i)

[Mikexcel]

I am working on an excel template. I want to eventually add columns BEFORE my cumulative column. I want to set it up so that my cumulative column updates certain text automatically after I insert columns. Basically, I want to know if there is a way to point to cells "i" places away from the cells that are calling the function, and are getting a return.

Here is an example of what I am trying to do: I have a cumulative colunm that iteratively says June - August. When I insert extra columns before the cumulative column, I want this cell that says June - August, to say June - September automatically. Since I will update the cell that is say i=3 places before the cumulative cell and populate it with the text September. Programatically, this is simply done with a for loop, but it seems that excel provides no for loop function.

It seems that this is a variation of SUMIF, but instead, I want something like the non-existent WRITEIF and the range would be three cells.

thanks

 

[Scott R]

In D1 put:
=A1&" thru "&OFFSET(D1,,-1)

 

[s-o-s]

On 2002-09-17 13:36, Mikexcel wrote:
I am working on an excel template. I want to eventually add columns BEFORE my cumulative column. I want to set it up so that my cumulative column updates certain text automatically after I insert columns. Basically, I want to know if there is a way to point to cells "i" places away from the cells that are calling the function, and are getting a return.

Here is an example of what I am trying to do: I have a cumulative colunm that iteratively says June - August. When I insert extra columns before the cumulative column, I want this cell that says June - August, to say June - September automatically. Since I will update the cell that is say i=3 places before the cumulative cell and populate it with the text September. Programatically, this is simply done with a for loop, but it seems that excel provides no for loop function.

It seems that this is a variation of SUMIF, but instead, I want something like the non-existent WRITEIF and the range would be three cells.

thanks

If you have A1 - C1 = Jun, Jul, Aug
In D1 Title = "Cumulative"
In A2..C2 your values
In D2 enter formula

=SUM(OFFSET(A2,0,0,1,MATCH("Cumulative",$A$1:$D$1,0)-1))

This will then search through the Title Row until it finds the word cumulative and sum all columns up to this one -1.