Ссылка на значение в закрытой книге Excel с использованием INDIRECT?

Я хочу сослаться на значение ячейки в другой закрытой книге с формулой (не VBA!). Имя листа сохраняется как переменная (в следующем примере C13 - это "Sheet2").

Если другой файл открыт, то работает следующее:

=INDIRECT("[myExcelFile.xlsm]" & C13 & "!$A$1")

Если файл закрыт, приведенная выше формула не работает, поскольку не указан абсолютный путь. Но я получил работу со следующим (обратите внимание на "вместо":

='C:\data\[myExcelFile.xlsm]Sheet2'!$A$1

Теперь я хочу заменить жестко закодированный "Sheet2" на динамическое ссылочное значение, то есть на C13 (как видно из первого фрагмента кода).

Кто-нибудь знает решение без использования VBA или других библиотек?

Ответ 1

Определенно нет способа сделать это с помощью стандартных формул. Тем не менее, сумасшедший вид ответа можно найти здесь. Это все еще избегает VBA, и это позволит вам получить ваш результат динамически.

Сначала создайте формулу, которая будет генерировать вашу формулу, но не добавляйте = в начале!
Давайте представим, что вы создали эту формулу в ячейке B2 Sheet1, и вы хотите, чтобы формула была оценена в столбце c.
Теперь перейдите на вкладку Формулы и выберите "Определить имя". Дайте ему имя myResult (или что вы выберете) и в myResult Относится к" напишите =evaluate(Sheet1!$B2) (обратите внимание на $)
Наконец, перейдите к C2 и напишите =myResult. Перетащите вниз и... вуаля!

Ответ 2

Проверить функцию INDEX:

=INDEX('C:\path\[file.xlsm]Sheet1'!A10:B20;1;1)

Ответ 3

= INDIRECT ( "'C:\Data" SheetName [ "&; A8 и усилитель]! $G9" )

где A8 содержит myExcelFile.xlsm

и G9 содержит ценные данные вашей исходной книги.

Ответ 4

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

https://www.extendoffice.com/documents/excel/4226-excel-reference-unopened-file.html

В формуле E:\Excel file\ - это полный путь к файлу нераскрытой книги, test.xlsx - это имя рабочей книги, Sheet2 - это имя листа, которое содержит значение ячейки, с которым вам нужно ссылаться, и A:A,2,1 означает, что ячейка A2 будет указана в закрытой книге. Вы можете изменить их на основе ваших потребностей.

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

=INDEX('E:\Excel file\[test.xlsx]sheetname'!A:A,2,1)

После применения этой формулы вы получите диалоговое окно "Выбрать лист", выберите рабочий лист и нажмите кнопку "ОК". Тогда на конкретную ячейку этого листка будет немедленно ссылаться.

Ответ 5

В Excel 2016, по крайней мере, вы можете использовать INDIRECT с полной ссылкой на путь; вся ссылка (включая имя листа) должна быть приложена ' символами.

Так что это должно сработать для вас:

= INDIRECT("'C:\data\[myExcelFile.xlsm]" & C13 & "'!$A$1")

Обратите внимание на закрытие ' в последней строке (т.е. '!$A$1 окруженный "")

Ответ 6

Проблема в том, что ссылка на закрытый файл работает с индексом (но не с индексом (косвенный (

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

C2=sheetname
if(c2=Sheet1,index(sheet1....),if(C2="Sheet2",index(sheet2....

Я сделал это за пять листов, это длинная формула, но делает то, что мне нужно.

Ответ 7

Если вам известен номер листа, который вы хотите ссылаться, вы можете использовать функцию ниже, чтобы узнать имя. Чем вы можете использовать его в функции INDIRECT.

Public Function GETSHEETNAME(address As String, Optional SheetNumber As Integer = 1) As String

    Set WS = GetObject(address).Worksheets
    GETSHEETNAME = WS(SheetNumber).Name

End Function

Это решение не требует открытия рабочей книги с рекомендациями - Excel откроет ее самостоятельно (но она будет скрыта).

Ответ 8

Вот вам метод динозавров на Office 2010.

Введите полный адрес, который вы хотите использовать, с помощью concatenate (метод "&" для объединения текста).

Сделайте это для всех необходимых вам адресов. Он должен выглядеть так:

= "=" & "'\ FULL NETWORK ADDRESS, включая [Название таблицы]" & W3 & "'! $w4"

W3 - динамическая ссылка на то, что я использую, W4 - это ячейка, которую я хочу получить с листа.

После этого запустите сеанс макросъемки. Скопируйте ячейку и вставьте ее в другую. Я вложил его в объединенную ячейку, и это дало мне классическую ошибку "Тот же размер". Но одна вещь, которую она сделала, - вставить полученный текст из моего конкатената (в том числе и дополнительный "=" ).

Скопируйте все, что вы сделали для этого. Затем перейдите в каждую вставленную ячейку, выберите текст и просто нажмите enter. Он обновляет его до активной прямой ссылки.

Как только вы закончите, положите курсор где-нибудь красивым и остановите макрос. Назначьте его кнопке, и все будет готово.

Это немного PITA, чтобы сделать это в первый раз, но как только вы это сделали, вы только что сделали квадратную привязку подходящей, которая даманировала круглое отверстие.