formulas will stay as entered.

[thmehr]

Hi
I asked a similar question last week, and it worked for part of my table by using an Index function.
But I have tried to adapt these two formulas with the index function and I get errors.

The two formulas in question.

=IF(Sheet6!$D$4>Sheet6!$E$4,"Up",IF(Sheet6!$D$4=Sheet6!$E$4,"No Change","down"))

=Sheet6!$E$4-Sheet6!$F$4

I have a table of weekly mortgage rates that I print out. This table gets rates from the next sheet.( sheet 6) To add weekly rates, I want to just click on column C and do an insert so I can add the weekly data.
But, when I do the column inset, these formulas above change by one column. Example C4 changes to D4. I have tried using the $ to lock it in, but excel still changes. On simpler cell reference, the Index function worked, but these two are more complex with IF statements and math.

Any help will be appreciated.;
Thanks
Ted

 

[arthurbr]

If I understand correctly, the col C reference has to stay the same after inserting ?
Try

=IF(INDIRECT("Sheet6!$C$4")>Sheet6!$E$4,"Up",IF(INDIRECT("Sheet6!$C$4")=Sheet6!$E$4,"No Change","down"))

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I would avoid the volatile function INDIRECT if possible

=IF(Sheet6!$D$4>Sheet6!$E$4,"Up",IF(Sheet6!$D$4=Sheet6!$E$4,"No Change","down"))

Instead, try

=CHOOSE(SIGN(INDEX(Sheet6!4:4,4)-INDEX(Sheet6!4:4,5))+2,"Down","No Change","Up")

=Sheet6!$E$4-Sheet6!$F$4

Instead, try

=INDEX(Sheet6!4:4,5)-INDEX(Sheet6!4:4,6)