Превратить путь к файлу в формулу с ссылками на ячейки.

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Превратить путь к файлу в формулу с ссылками на ячейки. (Формулы/Formulas)

Александр7034

Дата: Вторник, 27.06.2023, 15:06 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 58

Репутация: 0 ±

Замечаний: 40% ±

Имеется сводная таблица и куча файлов с отчетами excel в разных каталогах. В каталоге прописаны пути к файлам отчета в виде простой текстовой строки: C:\Отчеты\Таблица отчетов1.xlsx
Файлы отчетов различаются именами и каталогами, но внутри структура одинакова, в них таблицы одного размера и в одном и том же месте, на листе Отчет. Подскажите формулу которая путь к отчету в примере, конвертирует в ссылку. Обычно вручную через создать связь делаю, но уж много файлов нужно добавить. Пробовал через костыли сделать формулу

Код

="'"&ЗАМЕНИТЬ($E$1;ПРОСМОТР(;-1/(ПОИСК("\";ПСТР($E$1;СТРОКА($1:$99);99))=1);СТРОКА($1:$99));1;"\[")&"]Отчет'!B2"

в ней нахожу крайний "\" в пути к файлу и подменяю на "\[" что бы получилась гиперссылка. Потом через ДВССЫЛ текстовую формулу собираю в ссылку на ячейку, но она при закрытом файле отчета не работает. В примере показано. Или может предложите свой пример, желательно без макросов.

upd обновил фаил с примером там понятней расписал

К сообщению приложен файл: otchety.zip (17.9 Kb)

Сообщение отредактировал Александр7034 - Вторник, 27.06.2023, 15:29

Ответить

alexa1965

Дата: Вторник, 27.06.2023, 15:15 | Сообщение № 2

Группа: Проверенные

Ранг: Обитатель

Сообщений: 383

Репутация: 62 ±

Замечаний: 0% ±

2003> 2019 >2016

Добрый день, а что мешает установить связь с файлом например для ячейки Е3 сводной таблицы

Код

='[Таблица отчетов1.xlsx]Отчет'!$B2

и протянуть вниз

Главное не быть балабастиком

Ответить

Александр7034

Дата: Вторник, 27.06.2023, 15:22 | Сообщение № 3

Группа: Пользователи

Ранг: Участник

Сообщений: 58

Репутация: 0 ±

Замечаний: 40% ±

Испытываю трудность не с этим, а с тем как путь в текстовом виде C:\Отчеты\Таблица отчетов1.xlsx превратить в ссылку на этот фаил на определённый лист в нем на ячейку.

Сообщение отредактировал Serge_007 - Среда, 28.06.2023, 08:14

Ответить

Nic70y

Дата: Вторник, 27.06.2023, 15:25 | Сообщение № 4

Группа: Друзья

Ранг: Экселист

Сообщений: 9005

Репутация: 2369 ±

Замечаний: 0% ±

Excel 2010

без открытия файлов формулой это не сделать

ЮMoney 41001841029809

Сообщение отредактировал Nic70y - Вторник, 27.06.2023, 15:25

Ответить

alexa1965

Дата: Вторник, 27.06.2023, 15:25 | Сообщение № 5

Группа: Проверенные

Ранг: Обитатель

Сообщений: 383

Репутация: 62 ±

Замечаний: 0% ±

2003> 2019 >2016

Так это и есть связи. Я Вам показал в формуле, что значение ячейки Е3 ссылается на значение ячейки В2 листа Отчет файла Таблица отчетов1

Главное не быть балабастиком

Ответить

Александр7034

Дата: Вторник, 27.06.2023, 15:53 | Сообщение № 6

Группа: Пользователи

Ранг: Участник

Сообщений: 58

Репутация: 0 ±

Замечаний: 40% ±

Вы в примере привели структурированную в ссылку строку, с квадратными скобками и символами"'", тогда как в моем случае нужно путь без скобок превратить в понятной exel путь гиперссылок. То есть вручную я могу поставить эти символу, но как раз хочу уйти от ручного приписывания.

Сообщение отредактировал Serge_007 - Среда, 28.06.2023, 08:14

Ответить

Александр7034

Дата: Среда, 28.06.2023, 14:37 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 58

Репутация: 0 ±

Замечаний: 40% ±

Вот как так? ДВССЫЛ на ячейку с формулой выводит данные ячейки с той книги на которую ссылаюсь, но это пока книга на что ссылаюсь открыта. При закрытом втором файле ДВССЫЛ на него не работает. Пробовал макрофункцию вычислить, результат - ошибка, не верная ссылка на ячейку.

Сообщение отредактировал Александр7034 - Среда, 28.06.2023, 14:38

Ответить

Nic70y

Дата: Среда, 28.06.2023, 15:10 | Сообщение № 8

Группа: Друзья

Ранг: Экселист

Сообщений: 9005

Репутация: 2369 ±

Замечаний: 0% ±

Excel 2010

Цитата Александр7034, 28.06.2023 в 14:37, в сообщении № 7 (

)

Пробовал макрофункцию

ну значит Вы не совсем против VBA.
держите макрос - событие изменения ячейки E1.
макрос не открывает книгу, а "собирает" формулу
[vba]

Код

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E1")) Is Nothing Then
        'данные из
        a = Range("e1").Value                    'путь\файл
        b = InStrRev(a, "\")                    'ищем правый слеш
        с = Left(a, b) & "[" & Mid(a, b + 1, Len(a)) & "]Отчет'!"   'часть формулы без ссылки на ячейку
        d = "R[-1]"                    'смещение по строке
        e = "C2"                    'столбец
        'вставляем в
        f = "E3:E8"
        Range(f).FormulaR1C1 = "='" & с & d & e
    End If
End Sub

[/vba]

К сообщению приложен файл: 15.xlsm (15.7 Kb)

ЮMoney 41001841029809

Ответить

Превратить путь к файлу в формулу с ссылками на ячейки. (Формулы/Formulas)

Страница 1 из 1
1