Может ли Excel обновить ссылку на лист (как это можно сделать с ячейкой), когда я вставляю содержимое одного листа в другой?

Question

У меня есть документ, в котором я создаю новый лист, который представляет дни (должно быть так). Каждый лист имеет ссылку на одну и ту же ячейку на листе, который представляет предыдущий день. Таким образом, лист "Вторник" извлекает некоторые значения из листа "Понедельник" и так далее. Формула:

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33

и результат, который я вижу - что-то вроде Saldo from 1 June 2016, Amount $55.00

Если я создам новый лист и назову его Day05 и вставлю содержимое листа Day04 , есть ли способ обновить формулу, которая ссылается на Day03 на Day04? Прямо сейчас контент по-прежнему извлекает данные из листа Day03 .

Короче говоря, когда я копирую содержимое листа Day04 , я хочу видеть обновление формулы:

            Description                              Amount

="Saldo from "&TEXT($'Day04'.C4,"DD.MM.YYYY.")        =$'Day04'.F33

Прямо сейчас ссылка на имя листа не обновляется, и мой лист Day05 прежнему извлекает данные из Day03

            Description                              Amount

="Saldo from "&TEXT($'Day03'.C4,"DD.MM.YYYY.")        =$'Day03'.F33 //<-- WRONG is 03 ref!

Answer 1

Проблема состоит в том, чтобы создать новый лист, скопировать содержимое существующего листа и изменить ссылки на ячейки на второй существующий лист. Как говорит Сатья Мишра , это можно сделать вручную, изменив 'Day03'! на 'Day04'! после копирования на новый лист, Day05 .

Чтобы внести изменения автоматически при копировании с предыдущего листа на новый, давайте повторим проблему: создайте новый лист и скопируйте содержимое предыдущего листа. Используйте имя нового листа, чтобы сделать ссылки на предыдущий именованный лист.

Вот формула в текущем листе, которая получает содержимое ячейки F33 из предыдущего именованного листа. Например, если текущим листом является Day05 , формула получает F33 из листа Day04 .

=INDIRECT(CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33"))

Как работает формула

Формула использует INDIRECT чтобы получить значение ячейки из предыдущего листа. Если текущим листом является Day05 и нам нужна ячейка F33 , INDIRECT нужна строка, которая ссылается на ячейку F33 на листе Day04 . Нам нужна строка 'Day04'!F33 .

1. Получить имя листа для текущего листа.

Day05         =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,255)

2. Измените формулу, чтобы получить число в конце имени листа, изменив +1 на +4. Это пропускает 3 символа "День".

05            =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)

3. Вычтите 1, чтобы получить номер части предыдущего листа.

4            =MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1

4. Используйте TEXT чтобы дополнить число начальным нулем.

04           =TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#")

5. Используйте CONCATENATE чтобы создать новое имя листа со ссылкой на ячейку F33 .

'Day04'!F33  =CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","F33")

Используя формулу

Везде, где вы хотите получить значение ячейки из предыдущего листа, используйте формулу вместо ссылки на ячейку и измените последнюю строку в формуле на ссылку на ячейку. Например, чтобы использовать ячейку C4 для отображения описания (обратите внимание на C4 в формуле):

="Saldo from "&TEXT(INDIRECT(CONCATENATE("'Day",TEXT(MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+4,255)-1,"0#"),"'!","C4")),"DD.MM.YYYY.")

Результат Saldo from 01.06.2016.

Условия и предостережения

• Каждое имя листа должно заканчиваться двумя числами, например, Day02 , Day03 и т.д.
• Формула не будет работать, пока новый лист не будет иметь правильное имя. Неправильное имя листа вызывает # #VALUE! ошибки.

Answer 2

Я нашел решение благодаря комментарию Скотта Крейнера. Я предупреждаю вас, это будет длинная формула.

Везде, где у вас есть DayXX с номером XX, вы можете изменить XX на

&MID(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),FIND("y";MID(CE("filename",A1),FIND("]";CEL("filename";A1))+1,255))+1,2)-1

Это находит имя файла и ищет y от дня и извлекает номер из имени файла и вычитает 1 из него. Я могу объяснить это больше, если вы хотите, просто добавьте комментарий.

Answer 3

Excel изменяет ссылки на лист, когда мы меняем имя этого листа. Все ссылки переименовываются автоматически. Попробуем решить проблему с этой функцией: вместо переименования ссылок мы переименуем лист.

1. Откройте свою рабочую книгу с листами дней.
2. Создайте лист с данными для нового дня (скажем, "Day05"), как вы делали ранее. В конце скопируйте формулы из листа Day04, независимо от того, относятся ли они к Day03 после копирования, и сохраните их. Не закрывайте книгу еще!
3. Откройте другую книгу, которая может быть пустой, чтобы сохранить временную ссылку, и переместите лист Day05 (чтобы сделать это, щелкните правой кнопкой мыши Day05, выберите «Переместить или Скопировать ...» и выберите другую книгу как место назначения).
4. Удалите лист "Day04" и измените имя листа "Day03" на "Day04". Это сложная часть!

Теперь вы можете видеть, что лист "Day05" в новой рабочей книге будет иметь ссылки на Day04 (из первой рабочей книги) вместо Day03 из-за переименования. Далее, давайте переместим этот лист обратно в исходную книгу.

5. Закройте оригинальную книгу, отменив изменения, сделанные после последнего сохранения.
6. Снова откройте рабочую книгу и верните лист Day05, находящийся во временной книге. Ссылки все еще на Day04 (из текущей рабочей книги), потому что мы отменили изменения, а не переименовали лист обратно.

Вуаля! Это трюк, чтобы Excel переименовал ссылки для нас.

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

Надеюсь, поможет!

РЕДАКТИРОВАТЬ 1

Конечно, вы можете записать макрос с этим действием и даже адаптировать его, чтобы иметь возможность вызывать его быстрее.

Answer 4

Я не нашел способа сделать это автоматически. Но "Поиск и замена" (Ctrl+H), кажется, работает.