Изменить ссылку на ячейки др. листа, применить к столбцу

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Изменить ссылку на ячейки др. листа, применить к столбцу (Формулы/Formulas)

biomirror

Дата: Среда, 25.03.2015, 09:24 | Сообщение № 1

Группа: Проверенные

Ранг: Участник

Сообщений: 95

Репутация: 0 ±

Замечаний: 0% ±

Не смог придумать, как лучше назвать тему(. Кто придумает нормальное название, скажите плиз)) - мне для общего развития.:)
Есть файл с листами "черновик", "1", "2", "3" и т.д.
Необходимо, чтобы ячейка С6 листа 1 ссылалась на ячейку В2 листа "черновик", С7 листа 1 на В3 .
Также необходимо, чтобы ячейка С6 листа 2 ссылалась на ячейку С2 листа "черновик",С7 на С3.
А также чтобы ячейка С6 листа 3 ссылалась на ячейку D2 листа "черновик",С7 на D3.
и т.д... Листов много, в столбце С, который ссылается на лист "черновик", есть защищенные ячейки, то есть протянуть за один раз до конца столбца не получится.

Вопрос:
возможно ли, не протягивая формулу, поменять во всех ячейках (кроме защищенных, естественно) столбца С листа 2 (или 3,4,5...) ссылку на столбец C (D,E,F) листа "черновик" и сделать это максимально простым способом - с минимумом рутинных действий, а не ручками, протягивая каждую часть столбца.

Доп. вопрос:
если сделать двойной щелчок на маркере автозаполнения, то формула продлится до защищенной ячейки. Возможно ли то же самое сделать с помощью горячих клавиш?

К сообщению приложен файл: zamena.rar (23.3 Kb)

Ответить

Serge_007

Дата: Среда, 25.03.2015, 09:53 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Вариант:
1. Снимаете защиту листов
2. Группируете листы, кроме листа "Черновик"
3. Выделяете столбец С - F5 - Выделить - Формулы и вставляете формулу

Код

=ИНДЕКС(Черновик!$B$2:$O$81;ПОИСКПОЗ($A6;Черновик!$A$2:$A$81;);ПОИСКПОЗ(--ПРАВБ(ЯЧЕЙКА("filename";A1);ДЛСТР(ЯЧЕЙКА("filename";A1))-ПОИСК("]";ЯЧЕЙКА("filename";A1)));Черновик!$B$1:$O$1;))

4. Вручную поправляете формулы подитогов
5. Снимаете группировку
6. Ставите защиту листов

К сообщению приложен файл: biomirror.7z (21.8 Kb)

ЮMoney:41001419691823 | WMR:126292472390

Ответить

biomirror

Дата: Среда, 25.03.2015, 11:01 | Сообщение № 3

Группа: Проверенные

Ранг: Участник

Сообщений: 95

Репутация: 0 ±

Замечаний: 0% ±

1. Serge_007, а как вы сняли защиту? С меня пароль требуют (вкладка Рецензирование-Изменения-Снять защиту с листа).
2. В файле, который вы выслали, попробовал сделать так, как вы описали: сгруппировал листы, кроме "Черновика".
3. F5 - Выделить - Формулы, нажал Ок, выделяются все ячейки, где содержатся формулы. Куда вставлять формулу, которую вы описали, я не пойму.
4. Видимо мне надо что-нибудь по проще (чтобы смог использовать сам), я не понимаю, что такое формула подитогов и такие функции в формуле почти впервые вижу((

Я бы не хотел снимать защиту с ячеек, в них находятся формулы, которые также мне нужны.

Ответить

Serge_007

Дата: Среда, 25.03.2015, 11:30 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата biomirror, 25.03.2015 в 11:01, в сообщении № 3

как вы сняли защиту?

Макросом. Но можно и вручную. Если интересно - почитайте про защиту листа

Цитата biomirror, 25.03.2015 в 11:01, в сообщении № 3

Куда вставлять формулу

В выделенные ячейки

Цитата biomirror, 25.03.2015 в 11:01, в сообщении № 3

что такое формула подитогов

Это формула подитогов

Вы лучше меня должны знать где они в ВАШЕМ файле

Цитата biomirror, 25.03.2015 в 11:01, в сообщении № 3

бы не хотел снимать защиту с ячеек

С ячеек и не надо, надо с листа)

Цитата biomirror, 25.03.2015 в 11:01, в сообщении № 3

в них находятся формулы, которые также мне нужны

Вот эти формулы и надо поправить)

ЮMoney:41001419691823 | WMR:126292472390

Ответить

biomirror

Дата: Среда, 25.03.2015, 12:12 | Сообщение № 5

Группа: Проверенные

Ранг: Участник

Сообщений: 95

Репутация: 0 ±

Замечаний: 0% ±

Цитата Serge_007, 25.03.2015 в 11:30, в сообщении № 4

Макросом. Но можно и вручную. Если интересно - почитайте про защиту листа

Эмм... Из этой ветки я понял только, что снять пароль можно "несколькими паролями". А как снять, и эти пароли подобрать, я так и не понял.

Цитата Serge_007, 25.03.2015 в 11:30, в сообщении № 4

В выделенные ячейки

У меня ячейки выделяются так, как на скриншоте, и вставить формулу туда я не могу

Цитата Serge_007, 25.03.2015 в 11:30, в сообщении № 4

С ячеек и не надо, надо с листа)

И с листа не хотел бы)

Цитата Serge_007, 25.03.2015 в 11:30, в сообщении № 4

Вот эти формулы и надо поправить)

Я понял, что такое формулы подитогов и где они

Ответить

Serge_007

Дата: Среда, 25.03.2015, 14:41 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 16475

Репутация: 2749 ±

Замечаний: ±

Excel 2016

Цитата biomirror, 25.03.2015 в 12:12, в сообщении № 5

ячейки выделяются так, как на скриншоте

Предварительно выделите столбец С

ЮMoney:41001419691823 | WMR:126292472390

Ответить

biomirror

Дата: Четверг, 26.03.2015, 08:13 | Сообщение № 7

Группа: Проверенные

Ранг: Участник

Сообщений: 95

Репутация: 0 ±

Замечаний: 0% ±

Цитата Serge_007, 25.03.2015 в 14:41, в сообщении № 6

Предварительно выделите столбец С

Как его выделять, полностью? или только нужные ячейки?

У меня получается только следующее вставить формулу в ячейку, и протягивать ее. Все, больше никак.

Ответить

VEKTORVSFREEMAN

Дата: Четверг, 26.03.2015, 08:17 | Сообщение № 8

Группа: Друзья

Ранг: Ветеран

Сообщений: 772

Репутация: 130 ±

Замечаний: 0% ±

MS Office Excel 2010

Цитата biomirror, 26.03.2015 в 08:13, в сообщении № 7

Как его выделять, полностью?

Цитата Serge_007, 25.03.2015 в 14:41, в сообщении № 6

Предварительно выделите столбец С

кликнув мышкой по наименованию столбца "С"

К сообщению приложен файл: 3245422.jpg (48.4 Kb)

"Опыт - это то, что получаешь, не получив того, что хотел"

Сообщение отредактировал VEKTORVSFREEMAN - Четверг, 26.03.2015, 08:20

Ответить

biomirror

Дата: Четверг, 26.03.2015, 10:29 | Сообщение № 9

Группа: Проверенные

Ранг: Участник

Сообщений: 95

Репутация: 0 ±

Замечаний: 0% ±

VEKTORVSFREEMAN,
Я так и делаю, выходит окошко, что данная команда неприменима для несвязанных диапазонов.

Serge_007, не могли бы выслать файл, в котором снята защита (я так и не понял, как ее снять), но все остальное оставлено без изменений. Я на нем попробую.