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 toG4-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?