Formula Trouble When Sorting

Hi All,

I feel like I am overlooking something simple, so any advice would be greatly appreciated. I have a table of data which includes names, manager names, and data I am pulling from another tab. Now when I go to sort the names in "agent list" the formulas in my "offered" column don't update to accurately reflect the data corresponding with the name.

For example: David is in cell A22, the formula that pulls back his data is:
=IF($O$1=1,(SUMIFS(Data!$E:$E,Data!$A:$A,Dashboard!$B$4,Data!$D:$D,Dashboard!A22)),SUMIFS(Data!$E:$E,Data!$A:$A,">="&$P$2,Data!$A:$A,"<="&$P$3,Data!$D:$D,Dashboard!A22))

If I then sort the "Agent List" A-Z rather than the "offered" column switching to reflect the cell he is now in (a112) it keeps the cell he was originally in (a22) thus giving me the incorrect amount for offered.

I could use a pivot table, but then I would only be able to sort by names, and not by the data totals.

Thoughts?

I've also attached a copy for your reference. The tab named "Dashboard" is where you will find exactly what I am talking about.

You have saved me so much hassle today, Trevor. I am so thankful!