formulas change

[thmehr]

Hi,
I have sheet 1 that displays mortgage rates. I have sheet 2 that I copy my weekly rates too and sheet 1 gets filled in the updated rate. So it suppose to do that. I post new rates each week, by inserting a new column, and past the paste the rates. My issue is, the formals on sheet 1 change with insertion of the column on sheet 2. I have $ on the cell column and line numbers, but that is not preventing excel from advancing the column formula for cell c to cell d. I need to stop excel from advance the formula. How do I do that? Oh, my rate table is not listed as a table. It is just a blank sheet with data. I hope this makes sense.

 

[thmehr]

Hi,
I have sheet 1 that displays mortgage rates. I have sheet 2 that I copy my weekly rates too and sheet 1 gets filled in the updated rate. So it suppose to do that. I post new rates each week, by inserting a new column, and past the paste the rates. My issue is, the formals on sheet 1 change with insertion of the column on sheet 2. I have $ on the cell column and line numbers, but that is not preventing excel from advancing the column formula for cell c to cell d. I need to stop excel from advance the formula. How do I do that? Oh, my rate table is not listed as a table. It is just a blank sheet with data. I hope this makes sense.

Sorry, bad proof reading.
I have sheet 1 that displays mortgage rates. I have sheet 2 that I copy my weekly rates too and sheet 1 gets updated with the new rates. So, it supposed to do that. I post new rates each week to sheet 2, by inserting a new column. I insert on column C on sheet 2. My issue is, the formals on sheet 1 change with insertion of the column on sheet 2. I have $ on the cell column and line numbers of the formula, but that is not preventing excel from advancing the column formula for cell c to cell d. I need to stop excel from advance the formula or the column reference. How do I do that? Oh, my rate table is not listed as a table. It is just a blank sheet with data. I hope this makes sense.

 

[dreid1011]

Sorry, bad proof reading.
I have sheet 1 that displays mortgage rates. I have sheet 2 that I copy my weekly rates too and sheet 1 gets updated with the new rates. So, it supposed to do that. I post new rates each week to sheet 2, by inserting a new column. I insert on column C on sheet 2. My issue is, the formals on sheet 1 change with insertion of the column on sheet 2. I have $ on the cell column and line numbers of the formula, but that is not preventing excel from advancing the column formula for cell c to cell d. I need to stop excel from advance the formula or the column reference. How do I do that? Oh, my rate table is not listed as a table. It is just a blank sheet with data. I hope this makes sense.

You can use =INDIRECT() to get around the shift. Perhaps something like this:

=INDIRECT("Sheet2!C"&ROW())

Sheet 1:

Book1
	C
2	6
3	7
4	8
5	9
6	10
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=INDIRECT("Sheet2!C"&ROW())

Sheet 2:

Book1
	C	D
2	6	1
3	7	2
4	8	3
5	9	4
6	10	5
Sheet2

 

[thmehr]

thanks, I will try that later tonight