In short I have two worksheets.
Worksheet One ("Data Entry" in this case) has the reference data.
Worksheet Two has formulas that are adding various cells from Worksheet One as such:
='Data Entry'!C4+'Data Entry'!I4+'Data Entry'!K4

I had a need to sort the rows on the Data Entry worksheet using the row header (which is in column B in this case).
I anticipated that when row 4 was moved into a new position by way of a sorting function, now row 3, that the formula would change with it, but the formula stayed as above, referring to cells C4, I4, and K4.

I am surprised that the cell reference did not change accordingly to cell C3.

Is there a setting in Excel that can be changed as needed, so that a formula as above will change the reference accordingly when the data is sorted?