Sheet2が以下の場合…
┌─┬─┐
│  │A │
├─┼─┤
│ 1│ a│
├─┼─┤
│ 2│ b│
└─┴─┘
Sheet1なんかに以下のような数式を設定する。
┌─┬─────┐  ┌─┬─┐
│  │    A     │  │  │A │
├─┼─────┤  ├─┼─┤
│ 1│=Sheet2!A1│→│ 1│ a│
├─┼─────┤  ├─┼─┤
│ 2│=Sheet2!A2│  │ 2│ b│
└─┴─────┘  └─┴─┘
そこで、Sheet2を並び替える。
┌─┬─┐
│  │A │
├─┼─┤
│ 1│ b│
├─┼─┤
│ 2│ a│
└─┴─┘
すると、Sheet1も連動して並び替えて欲しいところが
律儀に参照先を維持してくれるので数式が書き替わって結果的に並び替えが効かない。
┌─┬─────┐  ┌─┬─┐
│  │    A     │  │  │A │
├─┼─────┤  ├─┼─┤
│ 1│=Sheet2!A2│→│ 1│ a│
├─┼─────┤  ├─┼─┤
│ 2│=Sheet2!A1│  │ 2│ b│
└─┴─────┘  └─┴─┘
ではどうやって並び替えを反映させるか？
絶対参照の「$」は効かない(同じ結果になる)。

INDIRECT()関数を使えばよい。
┌─┬───────────┐  ┌─┬─┐
│  │          A           │  │  │A │
├─┼───────────┤  ├─┼─┤
│ 1│=INDIRECT("Sheet2!A1")│→│ 1│ a│
├─┼───────────┤  ├─┼─┤
│ 2│=INDIRECT("Sheet2!A2")│  │ 2│ b│
└─┴───────────┘  └─┴─┘
これならSheet2がどうなろうが(並び替えしようが)、参照先は固定される。

なお、「=INDIRECT(Sheet2!A1)」みたいに引数に参照先を直接指定してはダメ。
「"」で囲んで文字列扱いとして指定する必要がある。