Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Ссылка на ячейку, которая меняется в зависимости от названия листа и "отслеживает" изменение позиции ячейки, на которую мы ссылаемся.
 
#1
22.11.2021 13:39:05

Доброго дня!

Прошу прощения за витиеватое название, но более ёмко и коротко не получилось. Итак, суть вопроса в следующем:
1. есть книга (реестр платежей) Excel с листами, которые носят имя согласно числу месяца, т.е. 1, 2 , 3, ... 29, 30, 31.;
2. каждый лист идентичен другому по форме и формулам (сделано это для того, что бы при внесении изменений на одном из листов, можно было просто скопировать весь лист и "повставлять" во все другие, а не править каждый лист отдельно);
3. на листах есть ячейки, данные в которые подтягиваются с предыдущего листа, например, входящий остаток денежных средств на начало дня, это остаток на конец  предыдущего дня (предыдущего листа);
4. что бы не править в ручную каждый лист, при каждом изменении формы листа, я воспользовался ДВССЫЛ и прочими формулами и подстановками (в примере будет видно), которые по итогу смотрят на название листа (а это по сути дата) отсчитывают назад 1 день и получают данные из нужного мне листа/ячейки. Но у этого метода оказался один существенный минус. При добавлении строк или столбцов в предыдущий лист, ссылки (формулы) не изменяются, т.е. НЕ ведут себя как обычная ссылка на ячейку (не отслеживают изменения положения искомой ячейки), а это необходимо, так как порою недостаточно количество строк по умолчанию и нужно добавлять строки, так как количество платежей превышает обычное.

В общем, я так и не смог придумать как это реализовать. У меня получается или формула отслеживающая имя листа, но в остальном статичная или ссылка которая меняется с изменением позиции ячейки, на которую ссылаемся, но не меняется в зависимости от названия листа.
 
 
 
#2
22.11.2021 13:48:30
Присвойте имя диапазону на листе 1 "Остаток". Формула примет вид:
Код
=ДВССЫЛ("'"&$O3&"'!Остаток")
Теперь можете добавлять строки и столбцы на листе 1, формула будет работать.
 
 
 
#3
22.11.2021 14:05:11
Спасибо огромное!
Вроде работает, но только на втором листе. Не очень понятно как быть с копированием на следующие листы. Поясните, пожалуйста, как правильно это сделать?
Т.е. это надо зайти на каждый лист и задать имя диапазона для каждого листа отдельно. Как я понял, при копировании листа такие данные как диапазон (имя) не переносятся по аналогии с копируемым листом или я что-то не так сделал.
 
 
 
#4
22.11.2021 15:42:56
Если я всё правильно понял, то такой вариант не подходит, так как возможность копирования с листа на лист является одним из критериев.
т.е. должно быть так, что лист 2 корректируется, далее выделяется весь лист, копируется, и вставляется на лист 3, 4 и так до 31го.
 
 
 
#5
22.11.2021 15:57:25
Цитата
написал:
надо зайти на каждый лист и задать имя диапазона для каждого листа отдельно.
Можно руками создать именованный диапазон. Или можно воспользоваться макросом.
Код
Sub AddNames()

    Dim sh As Worksheet
    For Each sh In ActiveWorkbook.Worksheets
        sh.Names.Add Name:="Остаток", RefersToR1C1:="='" & sh.Name & "'!R35C7"
    Next
End Sub
 
 
 
#6
22.11.2021 17:54:30
Возможно, я не до конца пояснил важные моменты.

Еще раз, важно:
1. что бы можно было внести корректировки в один лист и далее размножить на остальные путем ctrl+c и ctrl+v, а не править каждый лист заново руками.
2. ссылки с одного листа на другой "отслеживали" изменение положения ячеек, на которые ссылаются формулы, т.е. как это делают прямые ссылки на ячейки другого листа.

Допустим, мы добавили на втором листе дополнительные 5 строк сверху и формула на третьем листе, обращавщаяся до внесения изменений, на ячейку A1 на втором листе, должна уже обращаться на ячейку A5, тоже самое со столбцами.
В случае с именами диапазонов второе правило работает, но не работает первое.
 
 
 
Страницы: 1
Наверх