Sorting Trouble With If Statements

[crockcw]

I am having a sorting issue related to a column that contains several nested if statements and another column that references a cell on another worksheet.

The formulas work correctly when I have everything sorted by the employees name. However, whenever I change the way the worksheet is sorted my formulas are "out of whack". It is as if they are not referencing the correct cell. I am sure I am making this more complicated than it needs to be. Any help would be appreciated!

Here are examples of my formulas.

=IF(OR('Calc Details by TM'!D18="PTM3",'Calc Details by TM'!D18="PTM2",'Calc Details by TM'!D18="PTM1"),"PTM",IF(OR(D18="QTM2",D18="QTM1"),"QTM",IF(AND(D18="MTM1",F18=812),"METM",IF(AND(D18="MTM1",F18=814),"METM",IF(AND(D18="MTM1",F18=806),"MTM",IF(D18="MTM2","MMTM",IF(AND(D18="MTM1",F18=529),"MTM",IF(D18="STM","STM",IF(D18="Prod Field Analyst","Prod Field Analyst",IF(AND(D18="MTM1",F18=540),"METM"))))))))))

=(R2*'Wage Progression'!$E$21)+'Calc Details by TM'!R2

 

[jondavis1987]

Have you tried doing absolute cell references on all your cells in the formula?

 

[crockcw]

Each employee has a different job so I can't really use absolute references. I need the one if statement to capture all the possibilities on each cell as it goes down the list. I would think it should still work when I sort because all of the options listed in the if statement are captured even though the text changes in column D.