Текст формулы в одной ячейке, используемый в качестве ссылки для формулы в ячейках на разных листах

Question

Рабочая книга будет содержать десятки листов, и каждый лист будет иметь одну ячейку где-то на листе с функцией, которая возвращает стандартизированную фразу на простом языке, основанную на количестве поисков на этом листе. Функция может быть примерно такой:

="The start date was "&A1&" and resulted in "&B1

Каждый из этих листов имеет значения в A1 и B1, уникальные для этого листа, поэтому ссылки на ячейки в формуле идентичны для всех листов. Это означает, что A1 на каждом листе является уникальной датой начала, а B1 - уникальным результатом каждого листа.

Здесь проблема. Формула должна быть редактируемой на лету для всех листов, поэтому я хочу сохранить ее на одном « основном » листе, называемом ссылками в "основной" ячейке с именем ref_plainLang.

Здесь другая проблема. ГДЕ на каждом листе этот результат формулы простого языка должен отображаться отличается от одного листа к другому. На листе 1 строка простого языка должна появиться в C3, но на листе 2 она появляется в D6, и эти местоположения также могут измениться на лету. Я знаю, как это сделать с VBA, но хочу не-макро-версию, потому что болезненно менять местоположение на каждом листе с VBA.

Вот что не работает. В основной ячейке ref_plainLang я помещаю текст без знака =:

"The start date was "&A1&" and resulted in "&B1

На другом листе я печатаю

="="&ref_plainLang

и в этой ячейке теперь появляется полная функция, как это было бы напечатано

="The start date was "&A1&" and resulted in "&B1

но все еще текст.

Я много работал с Indirect(), так как это кажется естественным решением, но не могу заставить его работать. Если порядок текста и ссылок вряд ли изменится, я просто добавлю функцию полностью на каждом листе, сохранив каждый раздел фразы на простом языке как куски в отдельных ячейках в ссылках листа, что-то вроде этого:

[named: ref_plainLang1] A1 = The start date was 
[named: ref_plainLang2] B1 = A1
[named: ref_plainLang3] C1 =  and resulted in
[named: ref_plainLang4] D1 = B1

Затем соедините их вместе на листе 1, листе 2 и т.д. С помощью этого:

=ref_plainLang1&indirect(ref_plainLang2)&ref_plainLang3&indirect(ref_plainLang4)

Я не решаюсь использовать этот подход, если вместо этого он может быть сделан только с одной именованной ячейкой.

Answer 1

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

Предполагается: рабочий лист ref_plainlang со следующими определенными именами:

name              scope     refers to  
ref_startdate     workbook  =ref_plainlang!$A$1   
ref_resulted_in   workbook  =ref_plainlang!$B$1  
ref_text          workbook  =ref_plainlang$D$1

cell  value
A1    2018-01-01
B1    $100.00
D1    ="the start date was "&TEXT(ref_startdate,"yyyy-mm-dd")&" and resulted in "&TEXT(ref_resulted_in,"$0.00")

С этим на другом листе я могу ввести либо:

="the start date was "&TEXT(ref_startdate,"yyyy-mm-dd")&" and resulted in "&TEXT(ref_resulted_in,"$0.00") 

или же

=ref_text

И оба возвращают строку, the start date was 2018-01-01 and resulted in $100.00