Excel Sheet Linkage while Sorting

[razorback]

I have linked several sheets to a primary sheet in a spreadsheet. When I sort the primary sheet, the linked sheet cell references will adjust correctly, but the cell references within the primary sheet will not --- which causes major problems.
Before sorting data in the primary sheet, formula is:
=IF(K2=52,MRP52!E2,MRP12!E2)
After sorting data in the primary sheet, formula changes to:
=IF(K7=52,MRP52!E2,MRP12!E2)

The issue is the reference to cell K2 in the primary sheet ... it changes to cell K7. How do I keep the cell reference as K2 ??

OR should I have the !E2 changed to !E7 ????

Thanks Art Mueller

 

[Domski]

Hi,

Does it work if you change the reference to an absolute one:

=IF($K$2=52,MRP52!E2,MRP12!E2)

Dom

 

[razorback]

Excel Linkage & Sorting Response

Hi,

Does it work if you change the reference to an absolute one:

=IF($K$2=52,MRP52!E2,MRP12!E2)

Dom

No ... I believe the problem is the !E2 not changing to !E7 (as in my prior example). The K2 does change to K7, but the linkage remains !E2.

 

[Domski]

Get you. As far as I know references to other workbooks are absolute so they won't update when you sort the data in them.

I think you'll have to redesign your source workbooks to avoid the need for sorting.

Dom