Excel changing the formula when new column is inserted - how to STOP this?

**sunnydude959** · 11-26-2012, 06:38 AM

Hi Guys,

I have an excel spreadsheet which basically has columns of money coming in and money going out, and is in chronological order such that Left --> Right is Oldest --> Newest.
I have 3 rows; Money In, Money Out, and Balance.
The last row simply takes the balance from the previous date, add's "Money in" to it and subtracts the "Money out".

The problem I am having is that if I insert a new column (i.e. a new date entry sandwiched in between two existing date entries), the "balance" cell changes so that it refers to the same cell it was referring to before - i.e. it refers to the cell which is two columns to the left, after inserting the new column. I want to get it so that it always refers to one column to the left, even if a new column is inserted.

Any tips on how to do this?

Help will be much appreciated!

Thanks
Sunny.

**lmp** · 11-26-2012, 06:49 AM

Hello,

it's a bit hard trying to picture your sheet. Could you possibly upload a sample file with dummy data so we can look at the data structure?

From the description it sounds as if a column arrangement might not be the best way to approach the topic.

**sunnydude959** · 11-26-2012, 07:28 AM

I have attached the data as 'dummy_upload.xlsm' - Feel free to disable the macro's for security as they don't have an active part to play anymore.

The whole spreadsheet is in R1C1 format (as opposed to the A1, C4, etc. style referencing)

There are two tabs; both identical, but one of them with a new column inserted before column 9, such that the new column 9 is the empty 'inserted' column. The old column 9 is now column 10, etc.

I want the new column 10 to refer to the new column 9, instead of it referring back to column 8 (i.e. I want it to refer to one to the left, regardless what is inserted). The formula in new column 10 changed from =RC[-1]+R[-2]C-R[-1]C to =RC[-2]+R[-2]C-R[-1]C after the new column was inserted. It is THIS which i want to stop happening - I want it just ro remain as =RC[-1]+R[-2]C-R[-1]C regardless of whats inserted or not.

Many thanks
Sunny

**lmp** · 11-27-2012, 06:01 AM

I want it just ro remain as =RC[-1]+R[-2]C-R[-1]C regardless of whats inserted or not.

Well, that's not how Excel works. If you insert columns or rows, the formulas will update to retain the relationships. You will need to copy the formulas across again to populate the newly inserted column.