Trouble Sorting Formulas

[elotromateo]

I am having trouble sorting formulas that are referencing another part of the range that is being filtered. Currently, there are names in ColA, and values that are calculated based on ColA in ColC. So if Smith is in Row2, there is a number corresponding to that name in ColC, Row2. However, if I try and sort ColC from smallest to largest, it moves the cells around based on their current value, but no longer follows the correct name. So if Smith now resides in Row10, the cell that used to be in ColC, Row2 has also moved to Row10, but still references ColA, Row2, giving the incorrect value for Smith.

Is there any way to avoid this?

 

[Jerry Sullivan]

This link might help illustrate differences in sorting results when using Vlookup versus Index-Match.
http://www.mrexcel.com/forum/showthread.php?t=521087

If you post the typical formula you have in Col C (and Col A if those are not constants), that will make it easier for people to suggest alternatives.

 

[elotromateo]

Sorry, I should have given more information. Here is the formula:

=SUMIFS(Meter_Arr,Name_Arr,'Total Data'!$A3,Year_Arr,'Total Data'!C$1)

Meter_Arr is Column C, Bldg_Arr is Column A, and Name_Arr is Column F. All are references to a different sheet.

 

[Jerry Sullivan]

There might be a more elegant way to do this...but this seems to work:

In Cell C2 and copy down
=SUMIFS(Meter_Arr,Name_Arr,OFFSET('Total Data'!C2,0,-2),Year_Arr,'Total Data'!C$1)

The Offset function keeps the formula from tracking the original values
in Column A and instead just references Column A on the same row.

 

[elotromateo]

Thanks, that's not what I ended up doing, though it looks like it would work.

It turns out that having the sheet name in front of the cell reference sort of locked it onto that cell. I ended up making an if statement that allowed me to get rid of the sheet name since I no longer referenced another sheet first and now it works quite well.