Adding a row and relative addresses - how to make work like lotus.

[Eric Scantlebury]

Hello,

I am an old lotus 123 user who hasn't really done any cell functions in
excel. I am trying to put together a simple spreadsheet to run an ongoing
calculation using the following formula:

=IF(ISBLANK(C8),IF(ISBLANK(D8),"",+E6+D8),IF(ISBLANK(D8),+E6-C8,"Error"))

Basically I want to do the following taking out the formating parts of the
formula: =SUM(D2-B3+C3) and that is the formula populating cell D3. I am
then copying that formula down the sheet (say from D3 through D10). So far
so good. But when I try to insert a row or do a mass cut and past to "open
up a row" (say at row D5 which would then extend the sheet to row D11) excel
doesn't move the relative position of the formulas. It's almost as if excel
doesn't "Get" that I've inserted the row and adjust the formulas like Lotus
does. Obviously I'm doing something wrong.

After the row insertion or the cut and past operation I *should* receive
errors in my formulas until I put a formula value in cell D5. But that
doesn't happen. Excel keeps the formula that used to ocupy D5 (now
occupying D6) still looking at D4 when it should have adjusted all those
cells (D6 - D11) to key off a non existant (as of yet) value in the "new"
cell D5. This, of course, means I have to recopy *all* of the formulas back
down the D row instead of just copying the formula to the one cell.

What am I doing wrong?

TIA

 

[Jack Schitt]

You might change your basic formula to
=OFFSET(D3,-1,0)-B3+C3
That way all of your cell references refer to the same row as the row of the
calling formula. Inserting new rows should not interfere.
Not sure if that solves your particular problem.