Excel Sheet Linkage while Sorting

razorback

New Member
Joined
Jul 24, 2007
Messages
7
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

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

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

Dom
 

razorback

New Member
Joined
Jul 24, 2007
Messages
7
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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
 

Forum statistics

Threads
1,181,647
Messages
5,931,209
Members
436,784
Latest member
amuljono

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top