Hilfe bei Zelldropdown mit Gültigkeit

Forumthread: Hilfe bei Zelldropdown mit Gültigkeit

19.05.2020 07:11:09

ralf

Hallo Leute,
ich bin am verzweifeln. In meiner Arbeitsmappe werden einige Zelldropdowns mittels Gültigkeit
und Formel bereitgestellt. Leider verlieren beim Laden der Mappe die Listen ihren Bereich. Es bleiben nur die ersten 4 Einträge der Liste erhalten.
hier ein Beispiel der verwendeten Formel
=BEREICH.VERSCHIEBEN(Namen!$B$2;0;0;MAX(NICHT(ISTLEER(Namen!$B$2:$B$100))*ZEILE(Namen!$B$2:$B$100)) -1;1)
Komischerweise sind andere Dropdownlisten, die mit einer Index-Vergleich-Formel-Konstruktion erstellt sind, nicht betroffen.
An welcher Schraube muss ich drehen das die Listen funktionieren?
hilfesuchende Grüße
Ralf

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Hilfe bei Zelldropdown mit Gültigkeit

19.05.2020 07:32:42

Matthias
AW: hierzu ...

19.05.2020 08:14:04

neopa
AW: Nachtrag ...

19.05.2020 08:23:58

neopa
AW: Nachtrag ...

19.05.2020 22:36:30

ralf
AW: bitteschön owT

20.05.2020 08:51:28

neopa

AW: Hilfe bei Zelldropdown mit Gültigkeit

19.05.2020 07:32:42

Matthias

Hallo Ralf,
klick einfach in der Gültigkeitregel einmal ins Feld "Quelle" und dann "ok".
Schon ist die Liste wieder da.
Warum das jetzt so ist entzieht sich meiner Kenntnis. Konnte es aber nachvollziehen.
Gruß Matthias

AW: hierzu ...

19.05.2020 08:14:04

neopa

Hallo Ralf,
... zunächst, Deine Formel ist zu "überfrachtet" und zwar durch: MAX(NICHT(ISTLEER(Namen!$B$2:$B$100))*ZEILE(Namen!$B$2:$B$100)) Dieser Formelteil verlangt eingegeben in einer Zellformel normalerweise einen Matrixformeleingabeabschluss, wenn der MAX-Wert ermittelt werden soll.
Ein solcher Matrixformel ist aber in Dropdownzellen zumindest ab Version Excel2010 normalerweise nicht mehr zulässig, wenn sie vorab nicht als benannte Formel definiert und so eingesetzt wurde.
Das die Formel überhaupt in einer Dropdownzelle ab Version 2010 zugelassen wird, hängt mit der spezifischen (volatilen) Funktionalität von BEREICH.VERSCHIEBEN() zusammen, welches dann sich aber auch so oder so ähnlich auswirken wird, wie von Dir festgestellt.
Als Formel mit BEREICH.VERSCHIEBEN() in der Dropdownzelle ausreichend wie generell funktionsfähig und ohne das von Dir geschilderte Problem wäre folgende Formeldefinition:
=BEREICH.VERSCHIEBEN($B$2;;;ANZAHL2($B$2:$B$100);1).
Persönlich vermeide ich aber trotzdem die volatile Funktion BEREICH.VERSCHIEBEN() wo möglich und arbeite lieber mit der nicht volatilen Funktion INDEX(). In deinem Fall wäre es auch möglich aber deren Einsatz erfordert dann zuvor die Definition einer benannten Formel.
Gruß Werner
.. , - ...

AW: Nachtrag ...

19.05.2020 08:23:58

neopa

Hallo,
... in meiner Formelangabe fehlte noch der Bezug auf Dein Quelldatentabellenblatt. Dann müsste die Formel natürlich so lauten:
=BEREICH.VERSCHIEBEN(Namen!$B$2;;;ANZAHL2(Namen!$B$2:$B$100);1)

Sollten in Namen!$B$2:$B$100 jedoch Formeln stehen, die auch ="" beinhalten könnten, dann folgende Formel:
=BEREICH.VERSCHIEBEN(Namen!$B$2;;;ZÄHLENWENN(Namen!$B$2:$B$100;"?*");1)
Gruß Werner
.. , - ..

AW: Nachtrag ...

19.05.2020 22:36:30

ralf

Vielen Dank Werner
Die Formel hatte ich aus dem Netz und war schon froh dass ich diese gefunden hatte. Es ist abzusehen das diese Mappe von Anderen benutzt werden wird, die sich nicht mit den Gültigkeiten und Formeln auskennen und die Datei auch noch in Open Office benutzen wollen. Da war es mir lieber es ohne vba, benannte Bereiche, oder intelligente Tabellen lösen zu können.
Ich danke für die Tips und die Lösung.
Gruß Ralf

AW: bitteschön owT

20.05.2020 08:51:28

neopa

Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Hilfe bei Zelldropdown mit Gültigkeit

Forumthread: Hilfe bei Zelldropdown mit Gültigkeit

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread