sorting messes up data in other worksheets

Hi all:
I have an Excel 2007 file that uses several sheets. Some fields in sheet 1 are linked to data in sheet 2 (called BC Product Costs), so that when I change the data in sheet 2, sheet 1 automatically gets updated. A field value may look like this:
='BC Product Costs '!I11

When I move rows or columns around in sheet 2, or I add new rows or columns, sheet one automatically gets updated and there is no problem. Instead of I11, it may now say I14, but the value of I14 is still taken and placed in the appropriate field in sheet 1.

However, when I sort the data in sheet 2, the data in sheet 1 no longer get updated correctly. Say, I sort the data so that field I11 now becomes I16. In sheet 1, the corresponding field is still taking the data from sheet to, I11 instead of from I16. So it shows incorrect data !

I have found no way that allows me to sort the data in sheet 2 in a way without messing up the data in sheet 1. Anyone who can help me with this would make my day!

Hi breteler and welcome to the forum. If you can use lookup methods (vlookup, index, match etc.) you would be able to avoid the problem. The sort function isn't actually moving cells...it moves the values, so the references do not move with it. It will sort formulas along with it if they are included and updates the references within the sorted formulas correctly, but that's not helping your issue since you're referencing the sorted cells from an uninculded region. If you can't use a lookup method you could try writing a sort algorithm in VBA which would move entire rows...that could be fun. Thanks!

Another option would be to absolute the formulas in the sort range so that they maitain your original references after sorting