I have a data table with headers across two rows, i.e.

Row A: Project
Row B: Number

It is quite a large table with some cells referencing other worksheets within the same workbook - eg in cell 'AL3' the formula is:

"=SUMIF('Data-Nov'!$B;B,Data!$C3,'Data-Nov'!$AG:$AG)"

I am developing a Macro which selects the entire table and sorts it by the binary value in column A (i.e. it equals 1 or Nothing).

I am having difficulties getting the data to sort while maintaining its reference to the correct row number category. At the moment when I sort the table while indicating there is a header row the "Data!$C3" reference above is anchored to it's original reference and changes to return the value in the cell that originally held that data.

So now even though I want the formula in AL3 to return the value based on the new category of data in C3 it is returning the value based on what is in the old cell which has changed to C117 (very frustrating). So my formula is AL3 is now:

"=SUMIF('Data-Nov'!$B;B,Data!$C117,'Data-Nov'!$AG:$AG)"

What I want it to be is:

"=SUMIF('Data-Nov'!$B;B,Data!$C3,'Data-Nov'!$AG:$AG)"

I suspect it has something to do with the two header rows but I can't remove either of these as other macros are dependent on the information in both header rows.

Can anybody let me know how to get Excel to sort the table without anchoring the dependent formula values?