Lose Relative reference on sort
 

I have never been so frustrated with Excel in my life. I am very fluent in
Excel and have tried what I believe to be obvious fixes but I have several
spreadsheets (and I just got another one) where the relative reference
doesn't stay with the sort. I am using lookup and sumif formulas and when I
sort the records the formulas stay with the row and not the new variable that
gets sorted there. Here are two separate examples:

Before I sort
=SUMIF('Pick 1'!$A$10:$A$500,'Veh Data'!C7,'Pick 1'!$X$10:$X$500)*F7
After I sort
=SUMIF('Pick 1'!$A$10:$A$500,'Veh Data'!C98,'Pick 1'!$X$10:$X$500)*F7

F7 and Veh Data1!C7 are both relative but act differently when sorting -
because it is on another worksheet? I don't understand.

Here's another this one is an array
Before I sort
=SUM(IF(('MASTER LIST'!$A$2:$A$3000=Summary!$D$2)*('MASTER
LIST'!$A$2:$A$3000<=Summary!$D$3)*('MASTER
LIST'!$B$2:$B$3000=Summary!A3),'MASTER LIST'!$H$2:$H$3000,""))
After I sort
=SUM(IF(('MASTER LIST'!$A$2:$A$3000=Summary!$D$2)*('MASTER
LIST'!$A$2:$A$3000<=Summary!$D$3)*('MASTER
LIST'!$B$2:$B$3000=Summary!A79),'MASTER LIST'!$H$2:$H$3000,""))

Summary!A3 changes to SummaryA79 - doesn't sort with the record

Is the answer something really stupid I am not understanding about Excel or
relative/absolute. There has to be an answer - someone please help me.