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
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