Inserting Cols breaks formula

[Guest]

Using Excel 2000 on Win 98

I have numbers in cols B thru K and a summary formula (=K1-B1) in col A.
A new reporting period occurs, so I want to insert a col with new values.
I manually copy col B after col A (inserts a column) and adjust the value cells with new data. All existing data after col B shifts to right.
Col A now has formula L1-C1.

I want Col A formula to be L1-B1: adjust the first term but leave the second unchanged. I tried using K1-$B1 but this becomes K1-$C1. No good.
So each time I do this (fairly often) I have to change 20 various formula entries down col A to reset them to what I need.

Ideas? I'm sure the solution will be fairly simple but may involve a little-known (by me anyway) feature of Excel.

Thanks for all help.

MaryLou

 

[Paul]

Using Excel 2000 on Win 98

I have numbers in cols B thru K and a summary formula (=K1-B1) in col A.
A new reporting period occurs, so I want to insert a col with new values.
I manually copy col B after col A (inserts a column) and adjust the value

cells with new data. All existing data after col B shifts to right.

Col A now has formula L1-C1.

I want Col A formula to be L1-B1: adjust the first term but leave the

second unchanged. I tried using K1-$B1 but this becomes K1-$C1. No good.

So each time I do this (fairly often) I have to change 20 various formula

entries down col A to reset them to what I need.

Ideas? I'm sure the solution will be fairly simple but may involve a

little-known (by me anyway) feature of Excel.

Thanks for all help.

MaryLou

At the moment you copy column B and insert the new column after column A. If
instead you inserted it after column B, the formulas in column A will change
as you require.

 

[Guest]

MaryLo
You can use the INDIRECT function to keep Excel from changing you B1 reference

=K1-INDIRECT("B"&ROW(A1)

You could just use =K1-INDIRECT("B1") but I assume you will want to copy down the formula and have the row number change. The INDIRECT function will covert the text arguement into a cell reference

Good Luck
Mark Graese
(e-mail address removed)


----- MLou42 wrote: ----

Using Excel 2000 on Win 9

I have numbers in cols B thru K and a summary formula (=K1-B1) in col A
A new reporting period occurs, so I want to insert a col with new values
I manually copy col B after col A (inserts a column) and adjust the value cells with new data. All existing data after col B shifts to right
Col A now has formula L1-C1

I want Col A formula to be L1-B1: adjust the first term but leave the second unchanged. I tried using K1-$B1 but this becomes K1-$C1. No good
So each time I do this (fairly often) I have to change 20 various formula entries down col A to reset them to what I need

Ideas? I'm sure the solution will be fairly simple but may involve a little-known (by me anyway) feature of Excel

Thanks for all help

MaryLou

 

[Guest]

Hey guys,

Both ideas should work for my needs. The first idea of inserting right instead of left is beautiful in its simplicy if it really will work. The second idea using INDIRECT lets me learn something deep about Excel.

Thanks for these ideas.

MaryLou