Excel: Wert aus Dropdown-Menü in Zellbezug umwandeln

Gestern haben wir in diesem frisch aktualisierten Beitrag gezeigt, wie Sie beispielsweise in einer Personalliste die Abteilungen via Dropdown-Menü aus der Liste in einem anderen Tabellenblatt nachschlagen.

Das Problem hierbei, wie auch jemand in den Kommentaren zum Artikel erwähnt hat: Das Dropdown-Menü schreibt den nachgeschlagenen Text in die Zelle (z. B. «Logistik»). Es verweist aber nicht mit etwas wie =Abt!$A$5 auf die Zelle, in der nachgeschlagen wurde. Das hat den folgenden, vielleicht unerwünschten Effekt: Wenn Sie in der Abteilungen-Liste z. B. eine Abteilung umbenennen, kommt diese Änderung nicht in der Personalliste an. Sie müssten in der Personalliste einen Suchen/Ersetzen-Durchgang durchführen, um die Namensänderung der Abteilung dort ebenfalls zu berücksichtigen.

Falls Sie Ihre Liste ausbauen und in Zukunft lieber per Zellverweis auf die Abteilung verlinken wollen, geht dies für neue Einträge so: Klicken Sie hinter dem Mitarbeiternamen in die Abteilung-Zelle. Im Formelfeld oben tippen Sie ein Gleichzeichen (=) ein, wechseln zum Blatt mit den Abteilungen, klicken auf den gewünschten Abteilungsnamen und drücken Enter.

Nachgeschlagenen Wert durch Zellverweis ersetzen

Schön und gut. Aber Sie haben schon 30 oder mehr bestehende Einträge. Wie können Sie diese nachträglich möglichst automatisch umwandeln, damit diese ebenfalls einen Zellverweis enthalten?

Fügen Sie in der Personalliste eine Hilfsspalte ein. Bei uns ist diese jetzt Spalte E. Unsere Nachschlageliste, die wir fürs Dropdown-Menü erzeugt hatten, heisst «Abteilungen». Verwenden Sie die Funktionen ZELLE mit dem Infotyp Adresse, sowie INDEX und VERGLEICH:

=ZELLE("Adresse";INDEX(Abteilungen;VERGLEICH(D2;Abteilungen;0)))

Das macht Folgendes: Sie wollen als Ausgabe die ZELLE haben, und zwar die Zelladresse. Mit INDEX schlagen Sie in der Liste namens Abteilungen nach und vergleichen hierfür den Wert, der in D2 steht (dort haben wir die per Dropdown übernommenen Werte).

Als Resultat erhalten Sie die komplette Adresse der Zelle, aus der Sie den Wert ursprünglich nachgeschlagen hatten, zum Beispiel so etwas:

[20220912WertAusDropdownMenuInFormelUmwandeln.xlsx]Abt!$A$5

Jetzt fehlen noch zwei Dinge: Erstens das vorangestellte Gleichzeichen, damit Sie daraus eine Formel machen können. Sie könnten eine zusätzliche Hilfsspalte erzeugen und das Gleichzeichen mit dem Inhalt der Adresszelle verketten:

=VERKETTEN("="&E2)

Das lässt sich aber auch von Anfang an in eine einzige Formel packen:

=VERKETTEN("="&ZELLE("Adresse";INDEX(Abteilungen;VERGLEICH(D2;Abteilungen;0))))

Das führt zu diesem Zwischenstand, bei uns in Spalte E. Jetzt haben Sie eigentlich die Formel. Aber sie basiert auf reinem Text. Doch von hier aus ist es nicht mehr weit:

Fügen Sie eine weitere Hilfsspalte ein, bei uns ist es die Spalte F. Markieren Sie die Spalte mit dem provisorischen Formelergebnis, hier ist es Spalte E. Kopieren Sie Spalte mittels Ctrl+C (Strg+C), markieren Sie die Hilfspalte (F) und wählen Sie Einfügen/Werte einfügen (oder Inhalte einfügen/Werte).

Jetzt haben Sie Ihren Zellbezug! Nur steht er bislang als Text da. Excel erkennt ihn noch nicht als Formel. Sie könnten jetzt jede Zelle einzeln anklicken, F2 und Enter drücken, damit Excel die Zelle neu verarbeitet und den Inhalt als Formel erkennt. Aber das geht mit einem Trick schneller: Markieren Sie Spalte, in der die fertige, aber von Excel noch nicht erkannte Formel steht. Gehen Sie im Reiter Start via Bearbeiten/Suchen und auswählen zu Ersetzen. Wählen Sie unten hinter «Suchen» den Punkt In Spalten. Bei Suchen nach setzen Sie ein Gleichzeichen (=) – und bei Ersetzen durch ebenfalls! Dies nur, weil in jeder Zelle mindestens ein solches vorkommt. Benutzen Sie Alle ersetzen. Damit ändert sich der Inhalt nicht, aber Excel ist gezwungen, jede Zelle einmal «anzufassen».

Und das entspricht dem gleichen Vorgang, wie selbst in jeder Zelle F2 und Enter zu drücken.

Schon wandelt Excel den Text in eine Formel um. Verschieben Sie die Spalte mit den frischen Zellverweisen in Ihrem Tabellenblatt an die gewünschte Stelle. Die Hilfsspalten können Sie nun löschen. Voilà, in der neuen Abteilungsspalte sind die korrekten Zellverweise angekommen; und das Dropdown-Menü ist jetzt auch nicht mehr nötig. Bei Änderungen der Abteilungsnamen kommen diese jetzt automatisch in der Personalliste an.

Und LibreOffice Calc

Wie meistens bei Excel-Tipps probiere ich dasselbe auch in LibreOffice Calc aus. Und wie erhofft funktioniert es auch dort. Einzig beim letzten Schritt, dem Umwandeln der als Text dargestellten Formel in eine echte Formel gibts noch eine Anmerkung: Markieren Sie die umzuwandelnde Spalte. Benutzen Sie Bearbeiten/Suchen und Ersetzen. Auch hier ersetzen Sie das Gleichzeichen (=) durch ein Gleichzeichen. Verwenden Sie aber die Optionen Nur in Auswahl und greifen Sie bei Suchen in zum Eintrag Werte. Nach dem Klick auf Alle ersetzen haben Sie auch dort das gewünschte Resultat. (PCtipp-Forum)