☰

Может ли имя именованного диапазона быть динамическим?

tag-icon • tag-icon microsoft-excel named-ranges
Может ли имя именованного диапазона быть динамическим?

Я хотел бы, чтобы имя именованного диапазона в Excel было динамическим (в отличие от самого диапазона!). Я хотел бы знать, могу ли я определить имя диапазона с помощью формулы или ссылочной ячейки, чтобы имя менялось при изменении содержимого ссылочной ячейки.

НапримерЕсли я напишу список в столбце и напишу заголовок списка вверху столбца, вот так:

     A
1 *Colours*
2  Red
3  Yellow
4  Blue

Затем назовите диапазон ячеек списка (A2:A4) по названию списка (A1). Затем я хочу, чтобы имя диапазона автоматически менялось при изменении заголовка списка (т. е. имя диапазона = A1, и имя меняется при изменении содержимого A1).

Дополнительная информацияо моем конкретном случае: я использую именованные диапазоны для создания нескольких зависимых и динамических раскрывающихся списков в таблице Excel, которая предназначена для использования кем-то другим. Все настроено так, что если пользователь хочет добавить элементы в существующие списки (на рабочем листе вспомогательных списков), то раскрывающиеся списки (на рабочем листе основной таблицы) автоматически изменяются. Однако моя следующая задача — упростить для пользователя добавление новых списков. Мой план состоит в том, чтобы предоставить запасные столбцы списка (на рабочем листе вспомогательных списков), уже настроенные так, чтобы при заполнении они автоматически превращались в раскрывающийся список (на рабочем листе основной таблицы). Все формулы проверки данных (использующие именованные диапазоны) настроены для создания раскрывающихся списков на рабочем листе основной таблицы, недостающим шагом является автоматическое именование диапазонов списков, как только пользователь вводит новый заголовок списка. Мои формулы проверки данных должны использовать именованные диапазоны, поскольку раскрывающиеся списки, показанные в основной таблице, зависят от предыдущих выборов пользователя.

Буду очень благодарен за любые подсказки!

решение1

Это предполагает, что значение в A1 будет введено, а не установлено формулой. Введите следующий макрос события в область кода рабочего листа:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim str As String
    str = Range("A1").Text
    If Intersect(Range("A1"), Target) Is Nothing Then Exit Sub
    Dim n As Name
    For Each n In ActiveWorkbook.Names
        If n.RefersTo = "=Sheet1!$A$2:$A$4" Then
            n.Delete
        End If
    Next n
    ActiveWorkbook.Names.Add Name:=str, RefersTo:="=Sheet1!$A$2:$A$4"
End Sub

Поскольку это код рабочего листа, его очень легко установить и использовать автоматически:

  1. щелкните правой кнопкой мыши имя вкладки в нижней части окна Excel
  2. выберите «Просмотреть код» — откроется окно VBE
  3. вставьте текст и закройте окно VBE

Если у вас возникли какие-либо сомнения, попробуйте сначала на пробном листе.

Если вы сохраните книгу, макрос будет сохранен вместе с ней. Если вы используете версию Excel позже 2003, вы должны сохранить файл как .xlsm, а не .xlsx

Чтобы удалить макрос:

  1. откройте окна VBE, как указано выше
  2. очистить код
  3. закрыть окно VBE

Чтобы узнать больше о макросах в целом, см.:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

и

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Чтобы узнать больше о макросах событий (код рабочего листа), см.:

http://www.mvps.org/dmcritchie/excel/event.htm

Для работы макросы должны быть включены.!

ПРАВКА №1:

чтобы использовать A1 и B1 в качестве имени, просто замените:

str = Range("A1").Text

с:

str = Range("A1").Text & Range("B1").Text

Связанный контент