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

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

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

     A
1 *Colours*
2  Red
3  Yellow
4  Blue

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

Дополнительная информация о моем конкретном случае: я использую именованные диапазоны для создания нескольких зависимых и динамических раскрывающихся списков в электронной таблице 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. выберите View Code - откроется окно 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
Источник
Другие вопросы по тегам