730 Aufrufe
Gefragt in Tabellenkalkulation von peters Mitglied (460 Punkte)
Hallo zusammen,

die Möglichkeit, eine DropDown-Liste dynamisch durch Nutzung einer definierten Tabelle zu erweitern, ist mir bekannt.

Was ich allerdings benötige ist, die Möglichkeit einer dynamischen Erweiterung, wobei die Daten aus einer externen Datei gelesen werden sollen.
(Verschiedene Dateien greifen auf dieselben Daten zu, deshalb sollen diese zentralisiert und damit einfacher zu pflegen sein).

Hat da jemand eine Idee?

Grüße

Peter

6 Antworten

0 Punkte
Beantwortet von
Hallo Peter

ohne deine Datei zu kennen wird es schwer, dir eine Lösung anzubieten. Ganz grundsätzlich kannst du aber die Dropdownliste über die Formel Bereich.Verschieben dynamisch gestalten. Gib dazu im Feld Datenquelle eine Formel ein wie z.B. =BEREICH.VERSCHIEBEN($H$17:$H$31;0;0;ANZAHL2($H$17:$H$31;1))

Es wird eine Liste erzeugt deren Zeilenanzahl von der Anzahl der Einträge abhängt. Vielleicht hilft dir das ja weiter

Gruß Mr. K.
0 Punkte
Beantwortet von beverly_ Experte (3.3k Punkte)

Hi Peter,

du musst zuerst einen Namen (z.B. MeineListe) definierten, dem du die Formel für den dynamischen Bereich unter "Bezieht sich auf" zuweist. Wenn sich die Daten in einer anderen Mappe befinden, muss außerdem noch der Mappenname berücksichtigt werden. Angenommen deine Daten stehen in Spalte A der anderen Mappe (im Beispiel Mappe1.xlsx), dann würde die Fomel für den definierten Namen so aussehen:

=BEREICH.VERSCHIEBEN([Mappe1.xlsx]Tabelle1!$A:$A;0;0;ANZAHL2([Mappe1.xlsx]Tabelle1!$A:$A);1)

Anstelle BEREICH.VERSCHIEBEN kann man auch INDEX verwenden:

=[Mappe1.xlsx]Tabelle1!$A$1:INDEX([Mappe1.xlsx]Tabelle1!$A:$A;ANZAHL2([Mappe1.xlsx]Tabelle1!$A:$A);1)

Den definierten Namen weist du dann der Gültigkeitszelle als Liste zu.

Beide beiden Lösungen musst du beachten, dass die andere Mappe geöffnet sein muss - andernfalls funktionieren die Gültigkeitslisten nicht.

Falls die andere Mappe nicht geöffnet ist, muss man es auf andere Weise lösen.

Es stellt sich auch die Frage, WIE die Daten in der anderen Arbeitsmappe aktualisiert werden - sind es fix eingetragene Daten oder werden sie per Formel in die Mappe geholt, denn dann kann man ANZAHL2 nicht verwenden, weil diese Funktion auch Zellen berücksichtigt, deren Formelergebnis leer ist

Bis später, Karin

0 Punkte
Beantwortet von peters Mitglied (460 Punkte)

Guten Morgen Karin und Mr. K,

danke für Eure Ansätze.

Es ist tatsächlich so, dass die andere Datei geschlossen ist.

Vom Grundaufbau habe ich z.B die Daten in Arbeit.xlsx ein Blatt 'Arbeit'. Dort habe ich eine DropDown-Liste, welches sich die Daten aus dem Blatt "DropDown" A1:a10 der gleichen Mappe holt. Dort habe ich den Bereich der Daten als Tabelle definiert.

Das Blatt "DropDown" wird beim Öffnen der Mappe Arbeit.xlsx mit Daten gefüllt, die ich in der geschlossenen Datei "Daten.xlsx" eingepflegt habe.

Das Problem ist, dass die im Blatt "DropDown" definierte Tabelle nicht automatisch erweitert wird, wenn in der Datei "Daten.xlsx" weitere Daten ergänzt werden, der Bereich also z.B. auf  A1:A20 erweitert wird.
Ist halbwegs nachvollziehbar, was ich auszudrücken versuche?
Grüße
Peter
0 Punkte
Beantwortet von beverly_ Experte (3.3k Punkte)

Hi Peter,

das Problem ist, dass die Mappe bei dir geschlossen ist, aus der die Daten geholt werden sollen (was ich "befürchtet" hatte). Es bleibt dir dann nichts anderes übrig, als in der Mappe mit deiner Gültigkeitszelle eine separate Hilfs-Liste mit den Daten vorzuhalten, damit sich die Gültigkeitsliste darauf beziehen kann. Du musst dazu einfach nur die Daten per Formel mit Bezug auf die andere Mappe in die Hilfs-Liste holen - dazu in der ersten Zelle z.B. die Formel

=WENN('E:\Z_Test\[Mappe1.xlsx]Tabelle1'!$A1<>"";'E:\Z_Test\[Mappe1.xlsx]Tabelle1'!$A1;"")

die du beliebig weit nach unten ziehen kannst, auch wenn dann Zellen leer bleiben (Pfad und Mappenname anpassen!!).

Allerdings kannst du dann nicht mehr mit ANZAHL2 in dynamisch definierten Namen arbeiten, da diese Funktion auch die per Formel belegten leeren Zellen mit zählt - was ja nicht gewünscht ist. Dem kannst du aber Abhilfe schaffen, indem du in einer zweite Spalte der Hilfs-Liste die Anzahl an Einträgen zählst und das Maximum in deiner Namesdefinition anstelle ANZAHL2 verwendest. Trage dazu in die erste Zelle der 2. Spalte der Hilfs-Liste (z.B. B1) folgende Formel ein: =WENN(A1<>"";ZEILE(A1);"") und ziehe diese dann nach unten bis zu deiner letzten Zelle mit Formel in Spalte A. Den dynamischen Namen definierst du dann auf folgende Weise:

=Tabelle1!$A$1:INDEX(Tabelle1!$A:$A;MAX(Tabelle1!$B:$B);1)

Bis später, Karin

0 Punkte
Beantwortet von peters Mitglied (460 Punkte)

Guten Morgen, Karin!

Danke für Deinen Hilfsversuch, aber ich muss gestehen: Ich bekomme es nicht gebacken!

Wenn ich mit Anzahl2 arbeite, werden auch die leeren Zellen gezählt. Das lässt sich aber umgehen durch eine Hilfsspalte, in die der Wert "1" gesetzt wird, sofern das Feld nicht leer ist und mit der Summe der Spalte gearbeitet wird.

Zudem verstehe ich nicht ganz, wo ich die zuletzt genannte Formel einsetze.

Es ist mir nicht möglich, in irgendeiner Form auf diese Daten per DropDown zuzugreifen. angry

Hat das bei Dir schonmal funktioniert?

Grüße

Peter

0 Punkte
Beantwortet von beverly_ Experte (3.3k Punkte)

Hi Peter,

selbstverständlich funktionert es bei mir - andernfalls hätte ich dazugeschrieben dass ich es nicht getestet habe wink

Die zuletzte genannte Formel musst du für die Namensdefinition verwenden:

Register: Formeln -> Befehlsgruppe: Definierte Namen -> Schalter: Namensmanager -> Bezieht sich auf: =Tabelle1!$A$1:INDEX(Tabelle1!$A:$A;MAX(Tabelle1!$B:$B);1)

Im Anhang eine Beispielmappe wie ich es meine - Spalte A und B enthalten die Bezüge zur geschlossenen Mappe bzw. die Formel zur Ermittlung der Anzahl an vorhandenen Daten. Der Gegenpart geschlossene Mappe befindet sich bei mir im Ordner E:\Z_Test\ und heißt Mappe1.xlsx. In ihr sind in Spalte A die Begriffe aufgelistet, die in der Gültigkeitsliste in Zelle E2 vorgehalten werden sollen.

https://supportnet.de/forum/?qa=blob&qa_blobid=11109400586735129279

Bis später, Karin

...