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

Question

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

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

     A
1 *Colours*
2  Red
3  Yellow
4  Blue

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

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

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

Answer 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