Как остановить сортировку таблицы на одном листе от изменения переменной ячейки на другом листе, которая ссылается на ячейку в этой отсортированной таблице?

Question

Моя проблема заключается в следующем:

В Sheet2 я хочу сослаться на элемент (назовем его Widget 1) в Sheet1. Итак, в ячейке в Sheet2 у меня есть:

=Sheet1!B65

Это означает, что ячейка в Sheet2 всегда будет равна элементу, который находится в B65, даже если я вставлю строку, а Widget 1 окажется в B66. Ячейка на листе 2 теперь будет:

=Sheet1!B66

Это все хорошо, но если я сортирую по любому другому столбцу в Sheet1, то вместо изменения B65 на B <любой виджет строки 1, в котором сейчас находится>, ячейка в Sheet2 остается B65.

Разве сортировка не должна влиять на изменение ссылки на ячейку так же, как вставка строк? Я всегда хочу, чтобы ячейка в Sheet2 ссылалась на Widget 1, куда бы она ни перемещалась путем вставки и / или сортировки.

Answer 1

Ссылка на ячейку относится не к ее содержимому, а только к адресу ячейки.

Чтобы сделать то, что вы хотите достичь, вы можете создать вспомогательный столбец и указать нужное значение в вспомогательном столбце.

В Sheet2 вы можете использовать поисковую конструкцию с Index/Match, как

=INDEX(Sheet1!B:B,MATCH("select me",Sheet1!D:D,0))

Теперь вы можете отсортировать исходную таблицу по другому столбцу, и формула поиска по-прежнему будет возвращать правильное значение. Вы можете скрыть вспомогательный столбец, чтобы сохранить вашу книгу в порядке.