Страницы: 1 2 След.
RSS
Динамический выпадающий список с сортировкой по критерию
 
Всем привет!

В серое поле нужно внести список "Позиций" из таблицы (столбец D:D) с сортировкой по номеру (столбец E:E).
Положение поля со списком может меняться - это учитывает имя Data_List.
Положение таблицы может меняться, а список строк дополняться - это учитывает дин. диапазон Data.
Названия позиций и их номера могут меняться, также номера могут содержать пустоты ("") - список должен перестроиться с исключением пустот.

Как сделать?

Upd: во вложении - финальная версия, спасибо БМВ!
Изменено: Acid Burn - 16.10.2019 23:47:45
 
так как проверка данных в данном случае работает только с диапазоном листа, даже если он динамический, то на листе нужно формировать упорядоченный список и уже его использовать.
Изменено: БМВ - 16.10.2019 13:13:57
По вопросам из тем форума, личку не читаю.
 
БМВ, разве нельзя создать сортированный список DATA макросом?
Макрос повесить на событие обращения к именованной ячейке со списком...

Вот пример макроса создания списка в ячейке DATA_List без сортировки:
Код
Sub Macro2()
Dim i As Long
    ReDim Arr(1 To Range("DATA").Rows.Count)
    For i = 1 To Range("DATA").Rows.Count
        Arr(i) = Range("DATA").Rows(i)
    Next
    [DATA_List].Validation.Delete
    [DATA_List].Validation.Add Type:=xlValidateList, Formula1:=Join(Arr, ",")
End Sub
Изменено: Acid Burn - 16.10.2019 13:17:10
 
Цитата
Acid Burn написал:
разве нельзя создать сортированный список DATA макросом
Можно, но про макрос не было и слова, да и наполнение придется делать в проверку данных, а там ограничение по длине строки будет.
По вопросам из тем форума, личку не читаю.
 
Ограничение по длине строки 256 символов?
Если этой для каждой ячейки DATA, то нормально. Если в сумме для всех значений списка, то список длиннющий.
Изменено: Acid Burn - 16.10.2019 13:22:56
 
Цитата
Acid Burn написал:
Ограничение по длине строки 256 символов?
вроде больше если и есть, мог оказаться не прав, но зачем все эти телодвижения, если все функциями листа делается или хочется именно макросом?
По вопросам из тем форума, личку не читаю.
 
Функциями листа было бы ещё лучше! Но как?
 
Цитата
Acid Burn написал:
Но как?
А #2 ни о чем не говорит?
По вопросам из тем форума, личку не читаю.
 
Простите, не заметил и с ходу не понял. Вроде работает.
Реально ли убрать доп. столбец и восстановить ранее созданный динамический диапазон,
чтобы данные добавлялись в список автоматически?
Изменено: Acid Burn - 16.10.2019 14:29:33
 
Цитата
Acid Burn написал:
нужен доп. столбец
да
Цитата
Acid Burn написал:
меняем - всё перестаёт работать
что именно перестает?
По вопросам из тем форума, личку не читаю.
 
Acid Burn, Знакома ли Вам эта статья? Выпадающий список с быстрым поиском
Если вместо исключения лишних данных организовать их сортировку во вспомогательном диапазоне, то должно получиться именно то, что КМК Вам требуется...
 
Поправил пост выше - по запарке не въехал, что к чему. )
 
IKor, статью видел, но вроде на мою задачу не очень похоже.
И опять же используется доп. столбец, либо функции Фильтр и Сорт, которой нет в старых версиях Excel.
Изменено: Acid Burn - 16.10.2019 14:38:44
 
Acid Burn, SQL Запросом можно получить и список и отсортировать и поместить в проверку данных. вопрос только зачем? У вас закончились скрытые листы куда можно положить доп. столбец?
По вопросам из тем форума, личку не читаю.
 
БМВ, файл мега-здоровенный. Нужно какое-то простое и компактное решение.
Вот что-то похожее на макросах. Можно адаптировать под мою задачу?
Или как-то доработать Ваш пример под динамический диапазон?
Изменено: Acid Burn - 16.10.2019 16:46:13
 
VBA + SQL
Скрытый текст
Изменено: БМВ - 16.10.2019 16:59:14
По вопросам из тем форума, личку не читаю.
 
Цитата
Acid Burn написал:
И опять же используется доп. столбец,
Цитата
БМВ написал:
на листе нужно формировать упорядоченный список и уже его использовать
Так Вас устраивает дополнительный столбец или нет?
Сделайте его на отдельном листе и присвойте ему имя, которое используете в качестве источника данных для проверки-данных...
 
IKor, всё же решение БМВ мне больше нравится - никаких доп. столбцов, красота.

БМВ, спасибо, в Вашем файле всё работает!
Но есть проблема - данные расположились несколько иначе и списков будет несколько.
Понимаю, что в коде Test.xlsm надо поправить 3 строки, но не понимаю, как поправить... (
Изменено: Acid Burn - 16.10.2019 23:24:39
 
Скрытый текст
Изменено: БМВ - 16.10.2019 22:01:48
По вопросам из тем форума, личку не читаю.
 
БМВ, теперь понятно всё, кроме того, как заменить Лист1$j6:k65536 на динамический диапазон.
Ну, чтобы спокойно перемещать таблицу вверх-вниз-влево-вправо и забивать данные, не задумываясь.
Простите, сам понимаю, что вопросы идиотские, жёстко туплю после работы...

Задал имя Data = Лист1!$J$6:ИНДЕКС(Лист1!$J$6:$XFD$1048576;СЧЁТЗ(Лист1!$J$6:$J$1048576);СЧЁТЗ(Лист1!$J$6:$XFD$6)),
но cmd.CommandText = "SELECT F1 FROM [Data].Address (и FROM [Data]) не взлетает.
Изменено: Acid Burn - 16.10.2019 22:21:26
 
Цитата
Acid Burn написал:
не взлетает.
А должно? Изучите range.address , там 5 параметров, вам нужно 3 из них, и кажется еще убрать имя книги. + заменить ! на $ ну и конечно вставлять корректно.
Изменено: БМВ - 16.10.2019 22:29:21
По вопросам из тем форума, личку не читаю.
 
Описание Range.Address не помогло.
Сейчас динамический диапазон включает всю таблицу. И состоит из неволатильных функций.
Нужно как-то извернуться (через СМЕЩ или ДВССЫЛ) и оставить только 2 столбца или что?
Изменено: Acid Burn - 16.10.2019 22:44:02
 
Я ж не знаю что у вас там с диапазоном, да и зачем его делать динамическим?  Преобразовать примернро так,

Код
    cmd.CommandText = "SELECT F1 FROM [" & _
    Replace(Replace(Replace(Range("data").Address(False, False, , True), "!", "$"), "[" & ThisWorkbook.Name & "]", ""), "'", "") & _
    "] Where F2 Is Not Null order by F2 ASC"
    rs.Open cmd

ну уж ладно
=Лист1!$J$6:INDEX(Лист1!$K$6:$K$1048576;COUNTA(Лист1!$J$6:$J$1048576);1)
кстати дома на 2016 пришлось менять провайдера на
Provider=Microsoft.Ace.OLEDB.12.0
Изменено: БМВ - 16.10.2019 22:57:05
По вопросам из тем форума, личку не читаю.
 
БМВ, вариант cmd.CommandText из поста #23 сработал и с моим дин. диапазоном, Jet.OLEDB.4.0 на Office 2019 тоже работает.
Но Ваш вариант компактнее и продвинутее, оставлю его в качестве финального.
Спасибо огромное!
 
БМВ, если нажать Ctrl+A получим переполнение (Run-time error '6': Overflow).
Изменено: Acid Burn - 16.10.2019 23:46:40
 
поставьте CountLarge вместо Сount  или это сравнение уберите. Оно нужно чтоб лишний раз не дергать, но не критично.
По вопросам из тем форума, личку не читаю.
 
Поместил финальную версию в шапку, все свои промежуточные стёр.
По-моему, вполне достойно для Копилки идей.
Спасибо огромное!!!
Изменено: Acid Burn - 16.10.2019 23:50:16
 
Отловил ещё ошибку - "Run-time error '1004': Application-defined or object-defined error".
И это не ограничение выпадающего списка...
 
Возможно, в строке 24362 символов и 1183 значений. Срезал до 8000 прошло на 9000 уже сбой.
Но и глупо иметь такой выпадающий список. Что из него без поиска можно выбрать? Просто надо перейти на Combobox c фильтром, при этом добавить в запрос
Where F2 Is Not Null AND F1 Like "'%" & cmb.value &"%'"  и будет фильтровать по найденным по событию ввода в комбо.
Изменено: БМВ - 17.10.2019 09:49:06
По вопросам из тем форума, личку не читаю.
 
БМВ, понятно, пост был для информации о существовании лимита: по моим подсчётам 8189 символов.
По факту в списке будет не более 100 позиций, но не факт, что в длину они не превысят лимит...
Поэтому, если сделаете вариант Combobox c фильтром, буду благодарен!
Изменено: Acid Burn - 17.10.2019 10:44:47
Страницы: 1 2 След.
Наверх