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

Question

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

Например, скажем, лист n имеет ячейку, которая ссылается на ячейку в листе n-1. Если у меня есть листы 1, 2 и 3, я хочу, чтобы ячейка в 3 ссылалась на ячейку в 2. Затем, если я дублирую 3 (делая Лист 4), я хочу, чтобы та же самая ячейка в 4 ссылалась на ячейку в 3.

Обычно ячейка на листе 3 гласила бы: «Лист2»!A1. Затем, если я продублирую Sheet3 для создания Sheet4, мне нужно будет зайти в ту же ячейку на Sheet4 и изменить ее на «Sheet2»!А1 до «Лист3»!A1. Я предпочел бы, чтобы это изменение было сделано автоматически, когда я дублирую лист.

Извините за многословное объяснение, но есть ли способ автоматизировать этот процесс, а не делать это вручную? В настоящее время я использую предварительный просмотр Excel 2013, но у меня также есть Excel 2012.

Спасибо!

Answer 1

Я нашел свое решение прямо здесь: j-walk.com/ss/excel/tips/tip63.htm . Оказывается, макросы очень мощные! :)

Вы можете создать функцию с именем sheetoffset в VBA, чтобы сделать это

Function SHEETOFFSET(offset, Ref)
'   Returns cell contents at Ref, in sheet offset
    Application.Volatile
    With Application.Caller.Parent
        SHEETOFFSET = .Parent.Sheets(.Index + offset) _
         .Range(Ref.Address).Value
    End With
End Function

Answer 2

Щелкните правой кнопкой мыши на любой вкладке листа и нажмите "ПРОСМОТР КОДА".

Поместите следующий код в МОДУЛЬ 1. (если его там нет, просто нажмите ВСТАВИТЬ и МОДУЛЬ)

Код

Function oldsheet(rng As Variant)
    Prevsn = "Sheet" & Val(Mid(ActiveSheet.CodeName, 6, (Len(ActiveSheet.CodeName) - 5))) - 1
    With Sheets(Prevsn)
        oldsheet = .Range(rng)
    End With
End Function

А на новом листе следующая формула

=oldsheet("J30")

Публикуется как есть по ссылке на случай, если она будет удалена.

Ссылка получена с http://www.mrexcel.com/forum/excel-questions/399317-formula-referencing-previous-sheet.html

Answer 3

Использовать в ячейке, как = предыдущий (J30)

Function Prev(ByRef r As Range) As Variant
Application.Volatile
Prev = r.Parent.Previous.Range(r.Address).Value
End Function