Зависимые выпадающие списки

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

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

Зависимые выпадающие списки (Формулы/Formulas)

albertikhsanov00

Дата: Воскресенье, 30.10.2022, 16:02 | Сообщение № 1

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

Ранг: Новичок

Сообщений: 27

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

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

Здравствуйте, помогите пожалуйста с выпадающим списком. Суть такая: на листе "Перечень" в ячейках C4 и D4 вставлены выпадающие списки (ячейка D4 зависит от выбора С4), так вот проблема заключается в том, что при копировании ячейки D4 на ячейку D5, список остается зависит от ячейки С4, хотя при копировании ячейки С4 на С5 выпадающий список меняется. Так как копировать такие списки придется на 150 строк, то решение замены в формуле на правильную ячейку не подходит + такие перечни будут создаваться по несколько штук в день. Так вот как сделать чтобы при простом копировании ячейка D5 зависела от выбора С5 и на последующие строки? Заранее спасибо!!!

К сообщению приложен файл: 5330040.xls (94.0 Kb)

Ответить

Gustav

Дата: Воскресенье, 30.10.2022, 17:54 | Сообщение № 2

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

Ранг: Участник клуба

Сообщений: 2766

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

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

начинал с Excel 4.0, видел 2.1

Можно провернуть такой финт c минимальным использованием VBA. На листе "Перечень" нужно будет выделить ячейку, в которую будет постоянно записываться адрес в колонке C, соответствующий текущей строке активной ячейки, получаемый с помощью события листа SelectionChange.

Пусть такой служебной ячейкой будет ячейка J1 листа "Перечень". Тогда в модуль этого листа помещаем следующий несложный код события:
[vba]

Код

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("J1") = Me.Cells(Target.Row, 3).Address
End Sub

[/vba]
После этого идём в "Диспетчер имён" (по Ctrl+F3) и в нём для имени "Виды_оборудований" меняем в поле "Диапазон" формулу со старой, жёстко зафиксированной на ячейку Перечень!$C$4:
Код
=СМЕЩ(Виды!$A$2;ПОИСКПОЗ(Перечень!$C$4;Виды!$A$2:$A$39;0)-1;1;СЧЁТЕСЛИ(Виды!$A$2:$A$39;Перечень!$C$4);1)

на новую, с динамически изменяемым адресом:

Код

=СМЕЩ(Виды!$A$2;ПОИСКПОЗ(ДВССЫЛ(Перечень!$J$1);Виды!$A$2:$A$39;0)-1;1;СЧЁТЕСЛИ(Виды!$A$2:$A$39;ДВССЫЛ(Перечень!$J$1));1)

Всё! Можно пользоваться! Если мелькание адресов в ячейке J1 будет раздражать, можно либо скрыть ее значение белым цветом шрифта, либо переместить её куда-нибудь на правый конец таблицы ("с глаз долой")

В прилагаемом файле все вышеперечисленные манипуляции уже выполнены.

Код

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("J1") = Me.Cells(Target.Row, 3).Address
End Sub

Код

=СМЕЩ(Виды!$A$2;ПОИСКПОЗ(ДВССЫЛ(Перечень!$J$1);Виды!$A$2:$A$39;0)-1;1;СЧЁТЕСЛИ(Виды!$A$2:$A$39;ДВССЫЛ(Перечень!$J$1));1)

К сообщению приложен файл: LinkedDDL.xls (79.0 Kb)

МОИ: Ник, Tip box: 41001663842605

Ответить

СообщениеМожно провернуть такой финт c минимальным использованием VBA. На листе "Перечень" нужно будет выделить ячейку, в которую будет постоянно записываться адрес в колонке C, соответствующий текущей строке активной ячейки, получаемый с помощью события листа SelectionChange.

Пусть такой служебной ячейкой будет ячейка J1 листа "Перечень". Тогда в модуль этого листа помещаем следующий несложный код события:
[vba]

Код

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("J1") = Me.Cells(Target.Row, 3).Address
End Sub

Код

=СМЕЩ(Виды!$A$2;ПОИСКПОЗ(ДВССЫЛ(Перечень!$J$1);Виды!$A$2:$A$39;0)-1;1;СЧЁТЕСЛИ(Виды!$A$2:$A$39;ДВССЫЛ(Перечень!$J$1));1)

Автор - Gustav
Дата добавления - 30.10.2022 в 17:54

albertikhsanov00

Дата: Воскресенье, 30.10.2022, 18:17 | Сообщение № 3

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

Ранг: Новичок

Сообщений: 27

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

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

Цитата Gustav, 30.10.2022 в 17:54, в сообщении № 2 (

)

=СМЕЩ

Ответить

albertikhsanov00

Дата: Воскресенье, 30.10.2022, 18:21 | Сообщение № 4

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

Ранг: Новичок

Сообщений: 27

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

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

Gustav, огромное спасибо!!! Я правильно понял, чтобы для добавления еще несколько зависимых списков в строке нужно в коде дописать эти ячейки и изменить соответственно ячейку. Например, в ячейку Е4 добавляем список зависимый от ячейки D4, в код добавляем строчку про еще один служебную ячейку, и в "Диспетчер имён" изменяем соответствующие ячейки?

Ответить

Gustav

Дата: Воскресенье, 30.10.2022, 18:28 | Сообщение № 5

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

Ранг: Участник клуба

Сообщений: 2766

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

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

начинал с Excel 4.0, видел 2.1

Цитата albertikhsanov00, 30.10.2022 в 18:21, в сообщении № 4 (

)

Например, в ячейку Е4 добавляем список зависимый от ячейки D4, в код добавляем строчку про еще один служебную ячейку, и в "Диспетчер имён" изменяем соответствующие ячейки?

Вполне возможно. Но точно пока не знаю. Я сегодня делал это первый раз на практике. Будет конкретная задача - можно будет конкретно проверить гипотезу.

МОИ: Ник, Tip box: 41001663842605

Ответить

Gustav

Дата: Понедельник, 31.10.2022, 19:23 | Сообщение № 6

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

Ранг: Участник клуба

Сообщений: 2766

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

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

начинал с Excel 4.0, видел 2.1

Маленькое дополнение вдогонку. Еще вчера подумалось, что хорошо было бы, если бы при изменении значения в колонке C значение из подчиненного списка в колонке D тут же стиралось бы, поскольку остающееся в ней значение, скорее всего, противоречило бы новому значению в колонке C (т.е. становилось бы "не из того подчиненного списка").

Сегодня прикрутил этот несложный обработчик - на событие Change листа "Перечень" (раз уж мы уже задействовали VBA для нужд задачи, то почему бы не продолжить это делать):
[vba]

Код

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim newValue, oldValue
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.EnableEvents = False
    With Target
        newValue = .Value
        Application.Undo
        oldValue = .Value
        .Value = newValue
    End With
    If newValue <> oldValue Then
        Target.Next.ClearContents 'очищаем ячейку справа - в колонке D
        Target.Next.Activate 'и позиционируемся в нее - для выбора из подсписка
        SendKeys "%{DOWN}" 'и сразу открываем этот подсписок (Alt + стрелка вниз)
    End If
    Application.EnableEvents = True
End Sub

[/vba]Как говорится, аппетит приходит во время еды. Вот и мне во время "еды" захотелось, чтобы после выбора из списка нового значения в колонке C табличный курсор (активная ячейка) сразу прыгал бы в соседнюю справа колонку D, да еще и сразу бы любезно открывал зависимый подчиненный список. И, вроде, всё клёво получилось!

Код

К сообщению приложен файл: 9339224.xls (76.0 Kb)

МОИ: Ник, Tip box: 41001663842605

Ответить

СообщениеМаленькое дополнение вдогонку. Еще вчера подумалось, что хорошо было бы, если бы при изменении значения в колонке C значение из подчиненного списка в колонке D тут же стиралось бы, поскольку остающееся в ней значение, скорее всего, противоречило бы новому значению в колонке C (т.е. становилось бы "не из того подчиненного списка").

Сегодня прикрутил этот несложный обработчик - на событие Change листа "Перечень" (раз уж мы уже задействовали VBA для нужд задачи, то почему бы не продолжить это делать):
[vba]

Код

Автор - Gustav
Дата добавления - 31.10.2022 в 19:23

albertikhsanov00

Дата: Вторник, 01.11.2022, 06:37 | Сообщение № 7

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

Ранг: Новичок

Сообщений: 27

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

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

Gustav, ооо, круто, я как раз размышлял, как сделать так чтобы стиралось значение. Я, кстати, проверил теорию на практике для нескольких зависимых списков, и если делать служебные ячейки для каждого зависимого списка то все работает

Ответить

Зависимые выпадающие списки (Формулы/Formulas)

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