Absolute Reference Changes

[ATChurch]

I have the below formula in "sheet2" of a workbook ("Schedule" being the only
other sheet).

=IF(OR(Schedule!$A$5="Totals", Schedule!$A$4="Totals"), "",
IF(OR(Schedule!$B$4="START", NOT($C$1=""))*NOT(Schedule!$B$4="END"),
Schedule!$A$5-Schedule!$A$4, ""))

In creating this I initially used relative references so that I could copy
it over a large number of rows (10000) and have the row references update
automatically.

I then converted it to absolute references by using find/replace (eg Find: A
Replace: $A$).

I've done this over so many rows because the other sheet is a blank form and
it will be used multiple times with varying numbers of rows.

Because of this, I need to be able to insert blank rows into the middle of it.

That is where the problem arises. In the above formula, the refences
currently showing $A$5 don't appear to be absolute, they change when I insert
new rows. None of the other values do that.

 

[Bob Phillips]

If you insert a new row at row 5, all references to cells in row 5 and
beyond will adjust, relative or absolute.

 

[ATChurch]

Yes, I see that now. Thanks.

Anyway, as is "^£%*$ typical, the next question I actually found on here
answered it for me.

I inserted INDIRECT("X#") around every cell reference.