Справочный внешний файл в Excel с использованием текущего номера недели

E_McAndrew спросил: 31 июля 2018 в 09:56 в: excel

Я пытаюсь вытащить число из внешнего файла excel, который находится в подпапке, где хранится мой основной файл. Формула, которую я использую,

='"K:\Ops Report\Weekly Ops\2018\Week  "&INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)&"\Weekly Control\Data Control\Weekly Control\Data Control\"&[Template.xlsx]Membership'!$B$4

Эта броска #REF! Ошибка и меняет формулу на

='"K:\Ops Report\Weekly Ops\2018\Week  "&INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)&"\Weekly Control\Data Control\Weekly Control\Data Control\["&[Template.xlsx]Membership]"&[Template.xlsx]Membership'!$B$4

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

1 ответ

Есть решение
Pm Duda ответил: 31 июля 2018 в 04:41

Не уверен, что вы можете сделать это таким образом с закрытой книгой. Попробуйте это: https://groups.google.com/forum/#!msg/microsoft.public.excel.worksheet.functions/lzqhzexSvH4/dQAPVlM3RKwJ

Если целевая рабочая книга открыта, вы можете использовать INDIRECT().

=INDIRECT("'K:\Ops Report\Weekly Ops\2018\Week "&INT((TODAY()-(DATE(YEAR(TODAY()),1,2)-WEEKDAY(DATE(YEAR(TODAY()),1,1))))/7)&"\Weekly Control\Data Control\Weekly Control\Data Control\[Template.xlsx]Membership'!$B$4")

Подробнее здесь: https://exceljet.net/formula/dynamic-workbook-reference

В общем, вы можете использовать VBA для выполнения этого действия с закрытыми книгами.

E_McAndrew ответил: 31 июля 2018 в 12:08
Спасибо, я надеялся, что у меня есть некоторая синтаксическая ошибка, так как я думаю, что моя формула работает несколько. #Ref! указывает на то, что он что-то ищет, не так ли? Я бы предпочел не открывать вторую рабочую книгу, если это возможно, и я не хочу использовать VBA, поскольку это фактически для поддержки процесса RPA
Pm Duda ответил: 31 июля 2018 в 12:27
#REF! ошибка показывает, когда формула ссылается на недопустимую ячейку - здесь ссылка на закрытую внешнюю книгу недопустима.
E_McAndrew ответил: 31 июля 2018 в 12:39
Я не понимаю? Если я сохраню рабочую книгу, на которую есть ссылка, в общем месте и буду ссылаться на нее напрямую, это будет работать? Разница лишь в том, что я не хочу этого делать, я хочу, чтобы он извлек соответствующий файл за соответствующую неделю. Я также думаю, что тот факт, что это впоследствии меняет мою формулу, означает, что в ней есть небольшая ошибка?
Pm Duda ответил: 31 июля 2018 в 01:22
Да, тогда это должно работать. Проблема в том, что вы пытаетесь получить значение по ссылке на книгу, представленную в строке, но это невозможно, если вы не используете функцию INDIRECT(). Но эта функция требует, чтобы целевая рабочая книга была открыта.
E_McAndrew ответил: 31 июля 2018 в 01:34
Но вы сказали ранее, что ошибка #Ref связана с закрытой книгой. Тот факт, что я получаю эту ошибку, не означает ли это, что номер недели работает, но что-то не так с тем, как я ссылаюсь на ячейку?