Как исправить часть имени листа ссылки на ячейку Excel при копировании всего листа?

Question

Я хочу сослаться на другую ячейку в своем рабочем листе таким образом, чтобы при копировании листа я все еще указывал на ячейку на исходном листе. Если ссылка относится к ячейке на другом листе, имя листа сохраняется при копировании листа, но если ссылка находится на том же листе, Excel изменяет имя листа на целевой лист при копировании листа.

Я попытался определить имя ячейки, сделать ее область охвата всей книгой и сослаться на ячейку по имени, но, когда я копирую лист, Excel "услужливо" создает дублированное имя с областью действия только для листа назначения.

Я знаю, что могу использовать функцию "Косвенный", чтобы ссылаться на ячейку со ссылкой, которая не изменится при копировании листа, но я не хочу этого делать, потому что произойдет сбой, если на листе, содержащем структурные изменения, произойдет сбой. клетка.

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

Answer 1

Я не уверен, что полностью понимаю ваши требования / ограничения, но я все равно предложу это.  Определить функцию VBA

Function Original(Ref)
    Application.Volatile
    With Application.Caller.Parent
        Original = .Parent.Sheets(1).Range(Ref.Address)
    End With
End Function

Замените 1 в Sheets(1) номером листа, на который вы хотите сослаться, или его именем, например, Sheet("PeterK_Master") .  Теперь вам нужна копируемая ссылка на Sheet1!Q42 , просто скажите Original(Q42) .  Это будет автоматически обновляться, если вы вставите столбцы слева от Q или строки выше 42 (это то, что вы хотели, верно?).  И, если вы скопируете / перетащите его, он автоматически обновится, как любая другая относительная ссылка (на том же листе); и, конечно, вы можете остановить это, используя $ s.