[Solved] Inserting columns but not changing the formula

[Aweberman]

I have a simple spreadsheet listing values beginning in column G and proceeding to the right.

Columns C through F are used for calculating these columns.

I want to insert a new column where column G is without changing the formulas that are currently present in Columns C through F.

Example:
Cell C4 has the following formula: =G4-J4

When I insert a new column where G is, the cell changes to: =H4-K4

I want it to continue to say: =G4-J4
... no matter how many columns are inserted.

[FJCC]

I hope someone comes along with a better way but this seems to work. In C4 use the formula

Code: Select all

=OFFSET(G4;0;0) - OFFSET(J4;0;0)

[MrProgrammer]

Hi, and welcome to the forum.

I want it to continue to say: =G4-J4
... no matter how many columns are inserted.

=INDIRECT("G"&ROW())-INDIRECT("J"&ROW())
=OFFSET(A4;0;6)-OFFSET(A4;0;9)

I hope someone comes along with a better way but this seems to work. In C4 use the formula

Code: Select all

=OFFSET(G4;0;0) - OFFSET(J4;0;0)

When I inserted a column before G, that changed to =OFFSET(H4;0;0)-OFFSET(K4;0;0) using OOo 3.2. But references to column A (second formula above) would be left unchanged as long as the OP doesn't insert a column before A.

When a column is inserted before G, all references to columns G, H, I, J, … (whether relative, absolute, or mixed) are updated, but references to columns A through F are unchanged. If the OP plans to insert before A and wants to ensure that the formula still references G4 and J4, a simple solution is to use INDIRECT, however the cell containing the formula (formerly C4) will become D4.

Relative, absolute, and mixed references are discussed in section 8. Using formulas and cell references of Ten concepts that every Calc user should know.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Aweberman]

That seems to have done it.

I had the same issues with FJCC's fix as MrProgrammer did. But both of MrProgrammer's suggestions worked for the specific case I'm looking at. Thanks!

Relative, absolute, and mixed references are discussed in section 8. Using formulas and cell references of Ten concepts that every Calc user should know.

For the record, I did read that before posting but couldn't make heads or tails of it.