Формула. Как получить диапазон листов с условием в Excel?

Question

[Xambey]

Как получить диапазон листов со следующим условием для формулы в Excel? :
листы, имя которых содержит определённую подстроку , равную значению из некоторой ячейки.



P.S Т.е пользователь вводит значение в заранее определенную ячейку метку для поиска листов и автоматически формула применяется только к определенным листам. Для фото выше это будет например слово "Компонент", т.е это будут все листы содержащие подстроку - Компонент

Покажите пример такой формулы пожалуйста.

Comments

[h8nor]

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

[Xambey]

Дѣаволъ, я нашёл только ЛИСТЫ(), которая возвращает индексы листов

[h8nor]

Xambey, это встроенная функция Excel 2013 (справка убогая) или вы добавили макрос?
Попробуйте вывести список в колонку A, затем в колонке B пишите формулу с проверкой.

[Xambey]

Дѣаволъ, встроенная, я ничего не добавлял

[Xambey]

John Smith, Мне нравится вариант с CELL, но как это использовать внутри выражения для формирования диапазона?

[h8nor]

John Smith, через name manager и формулы тоже нужно использовать макрос "GET.WORKBOOK". Через формулу "ЯЧЕЙКА" способ подразумевает, что уже вручную созданы ссылки на ячейку каждого листа.
Не вижу способа получить список листов полуавтоматически именно формулами ((

[Xambey]

John Smith, нет, это не домашнее задание:) Пытаюсь автоматизировать отчет по тестированию и есть необходимость не писать некоторые формулы по 50 раз... И если получиться использовать список листов для СЧЁТЕСЛИ(range, object), то это сильно упростит работу моему отделу...

Если вас не затруднит, можете тогда добавить ответ с примером использования GET.WORKBOOK для такой задачи?

Раз такое дело, даже скину конкретный файл. На листе Summary в таблице Тесты функционала для второго столбца не хочется добавлять по отдельной формуле для каждого листа... Хотелось бы просто на листе Initial добавить 1 ячейку, куда записывается метка для поиска листов по имени - типо как "Компонент". И тогда все листы имеющие в имени слово Компонент обрабатываются в таблице тестов. Как то так:) Файл: https://yadi.sk/d/RS_MmckmDDOfNA

[h8nor]

Xambey, у вас в файле самописные макросы есть - пишите ими и не морочьтесь с формулами.
ЗЫ: Перед автоматизацией нужно изучить инструмент автоматизации, естественно.

Answer 1

[Eugene]

Допустим, у нас есть два листа:
Лист1
Лист2
На листе Лист1 в А1 пишем "Лист2" без кавычек.
На листе Лист2 в А1 пише "ФФФ" без кавычек
На листе Лист1 в А2 пишем формулу
=ДВССЫЛ(A1&"!A1")
Получаем в ячейке значение ФФФ.

Оно? А то вопрос не совсем понятен...

Comments

[Xambey]

Да, я так и сделал, потому как времени разбираться в VB нет :( Вы предложили решение в лоб. Я же имел ввиду получить список отфильтрованных по содержимому имени листов. Из того, что мне посоветовали я думаю решить эту задачу можно, как я хочу, через макросы с VB.

[Eugene]

Да. Только VBA. К сожалению, доступа к объектам экселя из формул нет. Мы можем получить информацию о текущем книге/листе/ячейке, но весь список того, что есть сгенерировать можно лишь с помощью макроса.