Kann der Name eines benannten Bereichs dynamisch sein?

Question

Ich möchte, dass der Name eines benannten Bereichs in Excel dynamisch ist (und nicht der Bereich selbst!). Ich möchte wissen, ob/wie ich einen Bereichsnamen mithilfe einer Formel oder einer Referenzzelle definieren kann, sodass sich der Name ändert, wenn der Inhalt der Referenzzelle geändert wird.

Z.BWenn ich eine Liste in eine Spalte schreibe und den Titel der Liste oben in die Spalte schreibe, wie folgt:

     A
1 *Colours*
2  Red
3  Yellow
4  Blue

Und dann benennen Sie den Zellbereich der Liste (A2:A4) nach dem Titel der Liste (A1). Dann möchte ich, dass sich der Bereichsname automatisch ändert, wenn der Titel einer Liste geändert wird (d. h. Bereichsname = A1 und der Name ändert sich, wenn sich der Inhalt von A1 ändert).

Zusatzinformationzu meinem konkreten Fall: Ich verwende benannte Bereiche, um mehrere abhängige und dynamische Dropdown-Listen in einer Excel-Tabelle zu erstellen, die von jemand anderem verwendet werden soll. Es ist alles so eingerichtet, dass sich die Dropdown-Listen (im Haupttabellen-Arbeitsblatt) automatisch ändern, wenn der Benutzer Elemente zu vorhandenen Listen hinzufügen möchte (im Arbeitsblatt mit den unterstützenden Listen). Meine nächste Herausforderung besteht jedoch darin, dem Benutzer das Hinzufügen neuer Listen zu erleichtern. Mein Plan ist, bereits eingerichtete Ersatzlistenspalten (im Arbeitsblatt mit den unterstützenden Listen) bereitzustellen, sodass sie sich beim Ausfüllen automatisch in eine Dropdown-Liste (im Haupttabellen-Arbeitsblatt) verwandeln. Alle Datenüberprüfungsformeln (mit benannten Bereichen) sind so eingerichtet, dass die Dropdown-Listen im Haupttabellen-Arbeitsblatt erstellt werden. Der fehlende Schritt ist die automatische Benennung der Listenbereiche, sobald der Benutzer einen neuen Listentitel eingibt. Meine Datenüberprüfungsformeln müssen benannte Bereiche verwenden, da die in der Haupttabelle angezeigten Dropdown-Listen von den vorherigen Auswahlen des Benutzers abhängig sind.

Ich wäre für jeden Hinweis sehr dankbar!

Answer 1

Dies setzt voraus, dass der Wert in A1 eingegeben und nicht durch eine Formel festgelegt wird. Geben Sie das folgende Ereignismakro in den Arbeitsblattcodebereich ein:

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

Da es sich um Arbeitsblattcode handelt, ist er sehr einfach zu installieren und automatisch zu verwenden:

1. Klicken Sie mit der rechten Maustaste auf den Registerkartennamen unten im Excel-Fenster
2. Wählen Sie „Code anzeigen“ – dadurch wird ein VBE-Fenster geöffnet
3. Füge das Material ein und schließe das VBE-Fenster

Wenn Sie Bedenken haben, probieren Sie es zunächst auf einem Probearbeitsblatt aus.

Wenn Sie die Arbeitsmappe speichern, wird das Makro mit gespeichert. Wenn Sie eine neuere Version von Excel als 2003 verwenden, müssen Sie die Datei als .xlsm und nicht als .xlsx speichern.

So entfernen Sie das Makro:

1. Rufen Sie die VBE-Fenster wie oben auf
2. Löschen Sie den Code
3. Schließen Sie das VBE-Fenster

Weitere Informationen zu Makros im Allgemeinen finden Sie unter:

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

Und

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

Weitere Informationen zu Ereignismakros (Arbeitsblattcode) finden Sie unter:

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

Damit dies funktioniert, müssen Makros aktiviert sein!

BEARBEITEN#1:

Um A1 und B1 als Namen zu verwenden, ersetzen Sie einfach:

str = Range("A1").Text

mit:

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