Страницы: 1
RSS
Выпадающий список с поиском без VBA
 
Наткнулся тут случайно и думаю может оказаться полезным.

Выпадающий список с поиском без VBA. Два минуса, это необходимость в проверке данных отключить вывод сообщения об ошибке, что не позволяет контролировать только ввод из отфильтрованного списка и то что пересчет не срабатывает при переходе на другую ячейку и нужно или водить часть слова или запускать пересчет. Используется функция CELL("content")  
=IFERROR(INDEX(E:E;SMALL(IF(ISNUMBER(SEARCH(CELL("contents");$E$2:$E$7))+(CELL("contents")=0);ROW($E$2:$E$7));ROWS($G$2:G2)));"")
ну и  =OFFSET($G$2;;;COUNTIF($G:$G;"?*"))
В примере все уже все набросано и добавлен облегченный вариант формирования отфильтрованного списка.

Собственно это скорее расширение или дополнение к https://www.planetaexcel.ru/techniques/1/9645/
Изменено: БМВ - 10.07.2020 08:52:28
По вопросам из тем форума, личку не читаю.
 
Добрый день!
Что б не создавать однотипные темы напишу здесь если автор темы не против.
Во вложении файл(пример), Выпадающий список с поиском, при поиске необходимого слова к примеру *изол* (заключаем в звёздочки) во вкладке Лист в выпадющем списке не все пункты правил из листа Правила.
Хотя на листе Правила их 10, на листе Лист их тоже 10 но со словом *изол* всего три находит. Может кто знает что нужно подправить в выпадающем списке?
Заранее спасибо!

 
Изменено: Евгений - 07.09.2020 08:53:16
 
Евгений,  В формуле не подправить. Нужно использовать отдельный диапазон, куда будет фильтроваться найденные строки. У вас находится первая и количество, но если они не подряд идут, то получается что в списке есть лишние и не т нужных.
По вопросам из тем форума, личку не читаю.
 
Отдельный диапазон, он ведь только если одна строка с раскрывающимся списком. Как её распространить на остальные строки?
 
Цитата
Евгений написал:
Как её распространить на остальные строки?
Это как раз и описано в первом сообщении. Только там все не просто. Динамический список перестраивается каждый раз.
Изменено: БМВ - 07.09.2020 11:43:01
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
Нужно использовать отдельный диапазон, куда будет фильтроваться найденные строки
А провести обработку в именованном диапазоне, не используя реальный столбец, можно?
Начал с варианта с функцией ФИЛЬТР:
- создал имя TMP=ФИЛЬТР(Table[Box];ЕЧИСЛО(ПОИСК(C13;Table[Box]));"не найдено"), где Table[Box] - столбец смарт-таблицы;
- в [C13] выбрал меню Данные - Проверка данных - Тип данных "Список" - Источник =TMP;
- при нажатии OK получил сообщение "При вычислении 'Источник' возникает ошибка", само собой при OK не взлетело.
Через ТРАНСП(TMP) и CМЕЩ(C13;0;0;СЧЁТЗ(TMP);1) тоже не вышло.

Update: И с вариантом от БМВ та же история...
Я так понял, что в источнике списка нужно указать первую ячейку диапазона (в нашем случае виртуального именованного).
Это вообще возможно или без доп. столбца не обойтись?

Update: Нашёл кучу вариантов наподобие этого - все с доп. столбцами.
Значит без доп. столбца не обойтись...
Изменено: Acid Burn - 07.09.2020 14:07:14
 
Цитата
Acid Burn написал:
А провести обработку в именованном диапазоне, не используя реальный столбец, можно?
можно, но в проверке данных это использовать не получится.
По вопросам из тем форума, личку не читаю.
 
Цитата
БМВ написал:
можно
А как?
 
Можно вырвано из конетекста. Что это даст?  - для выпадающего списка- ничего.
По вопросам из тем форума, личку не читаю.
 
БМВ, только сейчас понял, что под проверкой данных в посте #7 Вы имели ввиду список. )))
Простите, в попытке совместить интересное с работой утратил внимательность.
OK, значит оставлю вариант с доп. столбцом... Спасибо!
 
Цитата
написал:
Наткнулся тут случайно и думаю может оказаться полезным.
Действительно, очень полезные знания.
Скажите пожалуйста, будет ли он работь в книге с общим доступом?
Дело в том, что в  книге с общим доступом после попытки выбора значения из динамически сформировавшегося списка выскакивает ошибка:
"Не допускается копирование и перемещение формул или таблиц данных, находящихся в массивах общей книги."
Не могу понять куда копать  :cry:  
 
Цитата
написал:
Наткнулся тут случайно и думаю может оказаться полезным.

Выпадающий список с поиском без VBA. Два минуса, это необходимость в проверке данных отключить вывод сообщения об ошибке, что не позволяет контролировать только ввод из отфильтрованного списка и то что пересчет не срабатывает при переходе на другую ячейку и нужно или водить часть слова или запускать пересчет. Используется функция CELL("content")  
=IFERROR(INDEX(E:E;SMALL(IF(ISNUMBER(SEARCH(CELL("contents");$E$2:$E$7))+(CELL("contents")=0);ROW($E$2:$E$7));ROWS($G$2:G2)));"")  
ну и   =OFFSET($G$2;;;COUNTIF($G:$G;"?*"))
В примере все уже все набросано и добавлен облегченный вариант формирования отфильтрованного списка.

Собственно это скорее расширение или дополнение к  https://www.planetaexcel.ru/techniques
Добрый день, расскажите пжл подробнее как такое сделать
Страницы: 1
Наверх