Текст и формула в ячейке

[sorockinalex]

Ребят, помогите, всё обыскал, чёт не пойму как сделать:
Хочу сделать историю преобразования чертежей..
В ячейках ссылки:

___А_________B______________C______ __________________D
1 Чертёж 0
2 Чертёж 1 =A1
3 Чертёж 2 =B2 =INDIRECT(FORMULA(B2)) =INDIRECT(FORMULA(C3))
4 Чертёж 3 =B3 =INDIRECT(FORMULA(B3)) =INDIRECT(FORMULA(C4))...
5 Чертёж 4 =B4


Проблема в том, что FORMULF(B2) возвращает формулу, а не тектовое значение!!!
Как формулу преобразовать в текст?
Ожидаемый результат:
____А_________B_________C_________D _________E
1 Чертёж 0
2 Чертёж 1 Чертёж 0
3 Чертёж 2 Чертёж 1 Чертёж 0
4 Чертёж 3 Чертёж 2 Чертёж 1 Чертёж 0
5 Чертёж 4 Чертёж 3 Чертёж 2 Чертёж 1 Чертёж 0


P.S. операторы для LIBRE Office Calc, но приму советы по экселю, потом переделаю

P.S2 строк полно и не обязательно все чертежи идут подряд по изменениям, так что привязаться к номеру строки не получится. т.е. может быть вот так:
____А_________B_________C_________D _________E
1 Чертёж 0
2 Чертёж 1 Чертёж 0
3 Чертёж 2 Чертёж 1 Чертёж 0
4 Чертёж 6
5 Чертёж 3 Чертёж 2 Чертёж 1 Чертёж 0
6 Чертёж 11
7 Чертёж 4 Чертёж 3 Чертёж 2 Чертёж 1 Чертёж 0

[Aleksandr H.]

Так не пойдет?

[sorockinalex]

нет, так не пойдёт, т.к. строчки будут в разброс. Привязка должна идти к адресу ячейки, а не оффсетом

[Aleksandr H.]

Не влавливаю логики, почему в вас в В2 формула "=А2" а в ожидаемом результате там "Чертеж 0" а не "Чертеж 1".

Может в С3 формулу:

=INDIRECT(MID(FORMULA(B2);2;50))

?

[sorockinalex]

Да, там ошибка была - исправил.
То, что надо, спасибо! Только вот дальше теперь не знаю как продолжить...

____А_________B_________C_________D _________E
1 Чертёж 0
2 Чертёж 1 Чертёж 0
3 Чертёж 2 Чертёж 1 Чертёж 0
4 Чертёж 3 Чертёж 2 Чертёж 1 Чертёж 0
5 Чертёж 4 Чертёж 3 Чертёж 2 Чертёж 1 Чертёж 0

MID(FORMULA(B1);2;50)                  выдаёт A2
INDIRECT(MID(FORMULA(B1);2;50)) выдаёт значение в ячейке A2

А мне нужно значение в ячейке B2
Как хочу сделать:
1. Преобразовать MID(FORMULA(B1);2;50) в ссылку на ячейку (обозначим как x) - каким образом???
2. OFFSET(x, 0, 1) - беру правее на 1 ячейку
3. INDIRECT(OFFSET(x, 0, 1))

При копировании формулы на 10 столбцов вправо будет древовидно отображаться история создания чертежей, где чем правее по строке - тем древнее...

Текущий вопрос: как преобразовать адрес ячейки из текстового вида в адресовый...

[IgorGO]

как преобразовать адрес ячейки из текстового вида в адресовый...

ДВССЫЛ(...)

[sorockinalex]

Сообщение от IgorGO

как преобразовать адрес ячейки из текстового вида в адресовый...

ДВССЫЛ(...)

INDIRECT (ДВССЫЛ) выдаёт текстовое значение ячейки по её адресу.
А мне нужно преобразовать текст в адрес, чтобы сделать как я писал выше

[IgorGO]

цитата из справки по ДВССЫЛ:

ДВССЫЛ (функция ДВССЫЛ)
В этой статье описаны синтаксис формулы и использование функции ДВССЫЛ в Microsoft Excel.
Описание
Возвращает ссылку, заданную текстовой строкой. Ссылки немедленно вычисляются для вывода их содержимого

[sorockinalex]

Разберём на примере:
в B1 записана формула =A2
в B2 записана формула =An
n - произвольное число.
A2 имеет текст "Чертёж 1"
An имеет текст "Чертёж 0"

В столбец А вписываются новые обозначения чертежей
В столбец Б ставится ссылка на чертёж, из которого образован новый
Остальные столбцы автоматически высчитываются

____А_________B_________C_________D _________E
1 Чертёж 2 Чертёж 1 Чертёж 0
2 Чертёж 1 Чертёж 0
...
n Чертёж 0
...
m Чертёж 3 Чертёж 2 Чертёж 1 Чертёж 0


______________________результат действия

FORMULA(B1) =А2 в виде формулы
MID(FORMULA(B1);2;10) "A2" в виде текстовой строки
INDIRECT(MID(FORMULA(B1);2;10)) "Чёртёж 1" в виде текстовой строки

Если бы INDIRECT выдавал значение в виде ссылки (A2), тогда бы я сделал дальше:
OFFSET(INDIRECT(MID(FORMULA(B1);2;1 0)),0,1), получив адрес B2 и далее
INDIRECT(OFFSET(INDIRECT(MID(FORMUL A(B1);2;10)),0,1)) выдал бы мне "Чертёж 0"

Но INDIRECT выдаёт текстовое значение, а не адрес ячейки!

[sorockinalex]

Сделал вот так по коду символа:
C1:
=INDIRECT(ADDRESS(MID(FORMULA(B1);3 ;10);CODE(MID(FORMULA(B1);2;10))-63;4))

Скопировал формулу в соседний столбец
D1:
=INDIRECT(ADDRESS(MID(FORMULA(C1);3 ;10);CODE(MID(FORMULA(C1);2;10))-63;4))

- выводит "№ЗНАЧЕН"...