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?


--
Herman Merman
------------------------------------------------------------------------
Herman Merman's Profile: http://www.excelforum.com/member.php...fo&userid=8504
View this thread: http://www.excelforum.com/showthread...hreadid=526315