equivalent of copy / paste without doing so

[moira]

I have a formula in cell I3 that reads =sum(e37:e126). Easy enough.

I would like the formula in cell J3 to take the formula in I3 and move it to the right one column. In this case it would be =sum(f37:f126).

I can accomplish this thru a simple copy and paste. However, I would like the formula in cell J3 to grab the formula in cell I3 and take it to the right one column automatically without me having to copy and paste each time.

I envision this is a manipulation of offset / index / sum but I'm just not quite sure how to get there. Any thoughts would be greatly appreciated.

 

[texasaswang]

Have you tried the macro recorder? It should give you a forumula that you can use in the new cell.

So, from I3

activecell.offset(0,1).select
ActiveCell.FormulaR1C1 = "=SUM(R[34]C[-7]:R[123]C[-7])"

 

[moira]

Thanks. I'm trying to avoid using a macro. I figured I could get there with the help of vba but wanted to see if there was a formula that could do the trick?

 

[Marcelo Branco]

Hi,

Try this in I3
=SUM(OFFSET($E$37:$E$126,0,COLUMNS($A:A)-1))

drag to J3

HTH

M.