When I insert a new cell in a column, the formula changes.

[mike]

if I have a formula =A1+B1 and I insert a cell for A1 pushing current
A1 to A2, the formula changes to =A2+B1. What can I do to be able to
insert a new cell but keep the formula as =A1+B1?

Thanks,
Mike

 

[Jonah]

Pop a $ symbol in front of the column name to retain column titles

and/or in front of the row number to retain rows names.

Replicate 'til yours hearts content.

Jonah

 

[Frank Kabel]

Hi
try
=INDIRECT("A1")+INDIRECT("B1")

 

[simply_girl_9321]

a quick shortcut for making a cell value absolute (ie $A$1) so that it will
not change when you insert rows, is to use the function key F4 with the cell
address highlighted.

 

[mike]

It does not work using $. In regards to INDIRECT function, it works
but I can't drag it down for all rows...because it remains as
INDIRECT("A1")+INDIRECT("B1")when I drag it down. So if I want to do
the same for second row, third row and so forth, it will keep showing
INDIRECT("A1")+INDIRECT("B1") instead of
INDIRECT("A2")+INDIRECT("B2").

 

[Debra Dalgleish]

You could use the offset function. For example, if the formula is in
cell C1:

=SUM(OFFSET(C1,0,-2,1,2))

 

[mike]

Thanks Debra,

That is exactly what I was looking for. Just a quick follow up
question. What if I am looking for a subtraction instead of addition?
How will the
=SUM(OFFSET(C1,0,-2,1,2)) formula be written then?

thanks,
Mike

 

[Debra Dalgleish]

To subtract, you could use a formula similar to:

=OFFSET(C1,0,-2)-OFFSET(C1,0,-1)

in cell C1.