Выпадающий список с мультивыбором и исключением использованных значений

[rick1177]

Друзья, добрый вечер.
Сразу хочу отметить, что у этой темы есть кросс (http://www.planetaexcel.ru/forum/ind...olzovannykh-zn)

Обращаюсь к Вам, так как повисли на решении проблемы.
Обсуждение началось с того, что я искал способ решения следующей задачи:
"Вот на основе этого удачного примера (http://www.planetaexcel.ru/techniques/1/181/), но с использованием "умных" таблиц сделан список с мультивыбором.
Возник вопрос... А можно ли сделать так, чтобы выбранные элементы из вариантов выбора для этой конкретной ячейки пропадали?"

Проблема была весьма успешно решена Уважаемым Ренатом и Hugo.

Решение практически полное, однако в решении есть проблема:
При использовании в одной из ячеек в Умной таблице при протягивании в столбце не происходит распространения. Конечно проблема решается копированием/вставкой. Однако, поскольку я пытаюсь сделать удобным для обывателей, я спросил, нет ли возможности сделать это решение в виде функции, которая будет вставляться в качестве формулы при создании списка.

В качестве примера я привёл достаточно классическую ситуацию, например, при создании списка в формуле указываем =ДВССЫЛ("Таблица1[Таблица 1]") и получаем этот список, который распространяется при протягивании.
Хотелось, чтобы вместо =ДВССЫЛ("Таблица1[Таблица 1]") писалось что-то типа =Validation("Таблица1[Таблица 1]") и получалось, что проверялись бы уже существующие значения, записанные из списка, а в списке отражались уже только те, что ещё не использовались.

Т.е. ячейка пополняемая из списка с исключением уже использованных значений.

Наша работа приостановилась на таком варианте исполнения (прилагаю):

Что же пытались добиться:
1) есть таблица 1 и таблица 2 - базы для будущих списков;
2) В таблицу 3 заносятся значения и удаляются использованные из списка, доступного именно этой ячейкой;
Решение не работает при протягивании таблицы 3.
Предложено реализовать это в виде некой макрофункции (получилось вот что):

Public Function Validation(ByRef Target As Range, ByRef Massiv As Range) As String
    curval = Split(Target.Value, "; ")
    For a = 1 To Massiv.Rows.Count
        For b = LBound(curval) To UBound(curval)
            If CStr(Massiv.Item(a)) = curval(b) Then Exit For
        Next b
        If b > UBound(curval) Then
            If ValidForm = "" Then ValidForm = Massiv.Item(a) _
                Else ValidForm = ValidForm & "," & Massiv.Item(a)
        End If
    Next a
    If Len(ValidForm) > 0 Then Validation = ValidForm Else Validation = ""
End Function

Дальше хотелось в "Формула - Диспетчер имёл" создать формулу, что-то типа : =Validation(АДРЕС(СТРОКА();СТОЛБЕЦ( ));Таблица1)
а в формуле для списка написать "=Формула" и чтобы всё работало...
Но не работает.

Сам автор указал: "Если я правильно понял, то это будет примерно так, но у меня знаний маловато по пользовательским функциям, чего-то не работает (именованная переменная "Формула")... Не соображу правильно ли передаю в неё данные и возможно ли вообще такое."


Помогите, пожалуйста, дорешать....

[IgorGO]

однако...

не нужно оперировать такими понятиями как "умные таблицы" (понятными только Вам одному)
я в общем-то уже писал чего не стоит делать... но Вы абсолютно не обязаны обращать внимание на мои рекомендации

точно так же как все могут не обращать внимание на тему, в которой не сформулирована задача

[rick1177]

Как нет задачи... Igor, всё подробно расписано. Проблема поставлена. Зачем Вы так?

[rick1177]

Сообщение от IgorGO

однако...

"умные таблицы"

- термин не мой, а компании microsoft

Термин неоднократно упоминается в сети.

[IgorGO]

тоже правильно!
если я не понимаю - это не значит - все не понимают

если задача подробно расписана - сейчас повалят точные ответы!

[rick1177]

Может Вы зададите уточняющий вопрос, чтобы можно было пояснить?

[rick1177]

IgorGO .
Вот тут подсказывают:
"На programmersforum.ru IgorGO (URL: http://www.programmersforum.ru/member.php?u=10958 ), возможно, имеет ввиду, что в теме не правильно обозначена "проблема"... Скорее надо использовать что-то типа "UDF в именованной переменной / проверке данных". В теме на excelworld.ru, кстати, то же. Тут её ещё как-то можно (наверно) считать продолжением, а вот там уже новое название темы нужно. С уважением и наилучшими пожеланиями, Ренат."

[IgorGO]

исходя из таких соображений:
1.1. никто ничего не знает о вашей задаче
1.2. никто не будет изучать Ваши предыдущие темы на предмет "чем они пересекаются с текущей"
1.3. если задача общеизвестна - достаточно просто ее назвать, но тогда акцентировать внимание на особенностях Вашего варианта задачи
1.4. если это узкоспециализированная задача - нужно обьяснить ее суть
в любом случае - без описания задачи - нет решения.

необходимо описать:
2.1. исходные данные
2.2. результат
2.3. с помощью каких процессов (правил) исходные данные преобразуются в результат (лучше всего для этого подходит блок-схема алгоритма), нет алгоритма - описывайте на словах, на примерах

и приводя примеры кода, пишите для чего этот фрагмент предназначен.
не всегда по коду можно определить какую задачу этот код решает
особенно если код написан "криво" и так же криво решает задачу - то тут вообще по коду ничего не определить!

удачи!