0

I have a series of cells with very simple formula in them (e.g: G2=G4-G5) and I want that to remain fixed whatever I do to the sheet, in particular I want it to be resistent to insertion of a row between row4 and row5.

I tried all combinations of dollar-sign (eg.: G2=G4-$G$5), to no avail.

What am I doing wrong?

To explain context: I have the following:

G2=G4-G5
G4=C17
G5=<number>

At a certain moment I need to:

  • Insert a row between Row4 and Row5
  • Copy the value currently in G4 into the new (empty) G5
  • Make sure formula in G2 still points to G4-G5 (it must be zero right after this operation, of course)
  • This holds true for several (not all) columns.

After I solve this I'll try to automatize this procedurebut now I'm stump with the first line becoming G2=G4-G6 after row insertion.

UPDATE: I actually found a way to do it, but it seems an overkill:

G2=INDIRECT(ADDRESS(ROW()+2, COLUMN()), 1) - INDIRECT(ADDRESS(ROW()+3, COLUMN()), 1)

Is this really necessary?

2
  • 1
    That's two questions. Please ask them separately.
    – Chenmunka
    Dec 15, 2014 at 13:30
  • 1
    @Chenmunka: I rephrased making it a single question while leaving context in place.
    – ZioByte
    Dec 16, 2014 at 9:22

1 Answer 1

0

Any cell address will adjust to inserted/deleted rows regardless of whether you have annotated them as absolute references or relative. That is behavior by design. The method you provided in your original question edit is one way to get around the process but the cells addresses did not have to be resolved by the ADDRESS function; they could just as easily been written into the INDIRECT function as text.

G2's formula is: =INDIRECT("G4")-INDIRECT("G5")

Another option is the OFFSET function which hard codes numbers as offsets or staggers from a reference point. These hard-coded integers will not change with an insert or delete of a row/column.

G2's formula is: =OFFSET($G$1, 3, 0)-OFFSET($G$1, 4, 0)

Both OFFSET and INDIRECT are considered volatile functions. In this context, volatile means that the formula(s) will recalculate for every calculation cycle that the workbook experiences, regardless of whether a changed value will affect the formula's outcome or not. Functions in the volatile category make little difference on smaller worksheets or even in larger worksheets if the calculation is simple and the number of formulas involving the function is limited. However, with enough formulas and dependent formulas on a large worksheet at some point calculation lag becomes annoying. Here is a non-volatile solution.

G2's formula is: =INDEX(G:G, 4)-INDEX(G:G, 5)

Again, the row identifier is hard coded in much the same manner as the stagger is with OFFSET but this use of INDEX in not a volatile formula.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .