Как в гугл-таблице сделать многоуровневые ссылки? Каким образом в формулу поставить ссылку на ячейку ссылающуюся на другую ячейку?

Question

[Игорь]

У меня в гугл таблице условно есть 2 листа. На первом листе находятся данные, на второй лист некоторые из этих данных нужно выдернуть.

На втором листе в ячейке A1 стоит ссылка на ячейку A1 первого листа: ='Лист1'!A1
Так же на втором листе в ячейке A2 стоит ссылка на первую ячейку этого же листа =A1
Теперь мне надо в ячейке B2 второго листа вписать формулу, которая бы ссылаясь на ячейку A2 второго листа (которая в свою очередь ссылается на ячейку A1 второго листа, которая уже ссылается на ячейку A1 первого листа) выдало мне со смещением значение ячейки B1 первого листа.

После долгих мучений и ковыряний у меня получилось только нечто подобное, когда я в формуле ссылался сразу на ячейку A1 второго листа формулой:
=OFFSET(INDIRECT(REGEXREPLACE(FORMULATEXT(A1);"[=]";""));0;1)
Тогда мне выдаёт значение ячейки B1 первого листа.

Но вот как сделать что бы у меня это так же работало через ячейку A2 ?!

То есть что бы в формуле указать на ячейку ,которая ссылается на другую ячейку, которая в свою очередь ссылается на другой лист и вот что бы функция смещения работала для последней ссылки на ячейку из другого листа?

Надеюсь понятно объяснил, а то слишком запутанно получается...

Бьюсь над этим уже который день, ничего не выходит.

Comments

[hint000]

Надеюсь понятно объяснил

Нет.

Многоуровневые ссылки должны работать прозрачно, без каких-то сложностей и проблем. Делаете ссылку и всё, не важно один уровень или несколько. В чём суть проблемы - непонятно. Либо описывайте исходную задачу, либо хотя бы приводите пример таблиц со значениями (какой результат ожидается). А лучше и то и другое.

[Александр]

Опишите и покажите задачу, которую решаете.

[Игорь]

hint000, Александр, сейчас попробую объяснить что именно я хочу.
Мне надо что бы таблица автоматически рассчитывала тренировочный мезоцикл (план на 12-14 недель), что бы с моей стороны нужно было только вбить целевое упражнение и его повторный максимум.
Если сильно упростить и урезать мою таблицу, то получается в ней 2 листа.
На первом листе список соревновательных движений (столбец A), их повторный максимум (столбец B) и их процентовка (стоблец C-L).

Второй лист содержит ссылки с первого листа на выбранные целевые движения, которые будут отрабатываться в данном цикле (синие ячейки B1:D2)
Порядок тренировок и упражнений (ячейки A7:C19).
Рабочие веса в тех тренировочных микроциклах (зелёные и красные ячейки D7:F19)
Просто буквы алфавита (серые ячейки H1:Q2), что бы показать какие значения выводятся в рабочих весах второго микроцикла (красные ячейки).

Суть работы таблицы:
Во втором листе выбираю целевые движения, которые будут отрабатываться в данном тренировочным цикле (синие ячейки, которые ссылаются на первый лист с названиями упражнений).
Жёлтые ячейки автоматически проставляются выбранными упражнениями, т.к. они ссылаются на синие ячейки.
Значения в ячейках D7:F19 (зелёные и красные) тоже должны автоматически заполняться рабочими весами, с помощью функции смещения OFFSET, в зависимости от выбранных упражнений в ячейках B7:C19 (жёлтые), которые ссылаются на синие ячейки B1:D2, которые в свою очередь ссылаются на ячейки первого листа, столбца A, со списком упражнений. И вот эта функция OFFSET должна работать для конечных ячеек первого листа со списком упражнений, что бы выдать нужный вес на тренировке.

Реализовать это, ссылаясь на жёлтые ячейки у меня не получилось.
В итоге долгим методом проб и ошибок у меня получилось написать такую формулу:
=OFFSET(INDIRECT(REGEXREPLACE(FORMULATEXT($C$1);"[=]";""));0;8)
Она заполняет зелёные ячейки, ссылаясь на синие, которые в свою очередь ссылаются на первый лист и там функция смещения берёт нужные значения интенсивности тренировки и подставляет их.
Сделать то же самое, но ссылаясь на жёлтые ячейки у меня никак не получается.
Если тупо пользоваться этой же формулой, ссылаясь на жёлтые ячейки, то функция смещения уже отталкивается от синих ячеек и берёт значения со второго листа (серые ячейки H1:Q2), которые подставляются в красные ячейки.

Так вот как научить таблицу подставлять значения в зелёных и красных ячейках, ссылаясь на жёлтые, что бы те ссылались на синие, которые в свою очередь ссылаются на первый лист и от туда уже через смещение подставлялись нужные значения?!

Надеюсь в этот раз получилось объяснить.
На всякий случай вот ссылка на пример этой таблицы
https://docs.google.com/spreadsheets/d/12fhObhBwuR...

Answer 1

[hint000]

Вы переусложнили. Хотя да, я тоже не понял, почему ваша формула в одном случае работает правильно, а в другом случае нет.
На примере ячейки D7 вместо четырёх функций

=OFFSET(INDIRECT(REGEXREPLACE(FORMULATEXT($C$1);"[=]";""));0;8)

используйте всего одну функцию
=VLOOKUP($B7;'Лист1'!$A$3:$L$18;8+1;FALSE)
(специально написал не 9, а 8+1, чтобы было понятно, что это та самая 8 из OFFSET)

Comments

[Игорь]

Да, круто! Спасибо, работает!
Только тогда у меня возникает ещё одна загвоздка...
Моя таблица выглядит гораздо сложнее и мне нужно выдёргивать значения не только со сдвигом вправо, но так же влево, вверх и вниз.

[hint000]

Игорь, есть более универсальная функция, только надо указывать не смещение, а диапазон выводимых значений.
=XLOOKUP($B7;'Лист1'!$A$3:$A$18;'Лист1'!I3:I18)

[Игорь]

hint000, большое спасибо! Хоть натолкнули меня на мысль в сторону ВПР))
XLOOKUP в моём случае не совсем подходит, его можно использовать если нужно брать значения из столбцов левее, а мне помимо этого ещё надо выдёргивать значения не только левее и правее, но и одновременно выше и ниже))
Поэтому в моём случае подошла связка функций VLOOKUP и OFFSET.
Ещё раз благодарю!:)