Sorting Data with External References

[Herman Merman]

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?

 

[Harlan Grove]

Herman Merman wrote...
....

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)" ....
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.

It has nothing to do with the header rows.

This is just how Excel works. Excel sorts ranges by *moving* cells
rather than effectively copying and pasting them. If you have the
formula =C3 in cell AL3 then drag cell AL3 down to cell AL117 the
formula would still be =C3. That's effectively how Excel sorts.

Workarounds include using other referencing techniquest to refer to the
desired cell. In this case, if the range you're sorting is in the Data
worksheet, replace your AL3 formula with

AL3:
=SUMIF('Data-Nov'!$B;B,INDEX($C:$C,ROW()),'Data-Nov'!$AG:$AG)

and fill down into the rest of the used portion of column AL3.

If sorting moved AL117 to AL3, the formula would remain

=SUMIF('Data-Nov'!$B;B,INDEX($C:$C,ROW()),'Data-Nov'!$AG:$AG)

but the value of ROW() would have changed from 117 to 3, so the INDEX
call would still refer to the value in column C in the same row.