Microsoft Excel

Herbers Excel/VBA-Archiv

Gültigkeitsliste verringern


Betrifft: Gültigkeitsliste verringern von: Rudolf Perkams
Geschrieben am: 26.02.2019 11:39:14

Hallo,
ich möchte beispielsweise in den Zellen A1, B4, C7 Dropdown-Felder einrichten, die auf dieselbe Liste zugreifen, z.B. aaa in H1, bbb in H2, ccc in H3.

Bei der Auswahl in den Dropdown-Feldern sollen jedoch nur noch die Werte der Liste erscheinen, die in den anderen Dropdown-Feldern noch nicht ausgewählt wurden.
Ich dachte das Problem z.B. mit folgenden Formeln zu lösen:
in Zelle H1 =WENN(ODER(A1="aaa";B4="aaa";C7="aaa");"";"aaa")
in Zelle H2 =WENN(ODER(A1="bbb";B4="bbb";C7="bbb");"";"bbb")
in Zelle H3 =WENN(ODER(A1="ccc";B4="ccc";C7="ccc");"";"ccc")
Diese Lösung funktioniert durchaus, doch sind diese Formeln bei nur drei Dropdown-Feldern und drei Werten in der Liste in diesem Beispiel noch übersichtlich. Je länger die Gültigkeitsliste ist und je mehr Dropdown-Zellen auf diese Liste zugreifen, desto komplizierter wird es.

Gibt es eine elegantere Lösung ohne VBA?
Gruß Rudolf

  

Betrifft: AW:(D)eine Beispieldatei könnte hilfreich sein owT von: neopa C
Geschrieben am: 26.02.2019 13:08:17

Gruß Werner
.. , - ...


  

Betrifft: AW: AW:(D)eine Beispieldatei könnte hilfreich sein owT von: Rudolf Perkams
Geschrieben am: 26.02.2019 15:03:01

Hallo,
anbei die Beispieldatei entsprechend meinen Ausführungen in der Frage:
https://www.herber.de/bbs/user/127946.xls

Gruß Rudolf


  

Betrifft: Lösungsvorschlag von: Beverly
Geschrieben am: 26.02.2019 15:37:52

Hi Rudolf,

benutze eine Hilfstabelle (Bereich E1:H3) und definiere einen Namen, den du als Quelle für die Gültihkeitsliste angibst:


Tabelle1

 ABCDEFGH
1    aaaaaa1
2    bbbbbb2
3    cccccc3
4        
5        
6        
7        

verwendete Formeln
Zelle Formel Bereich N/A
F1:F3=WENN(ZÄHLENWENN(A$1:C$8;E1)=0;E1;"")  
G1:G3{=WENN(ISTFEHLER(WENN(ZEILE(F1)>ANZAHL2(F:F);"";INDEX(F:F;KKLEINSTE(WENN(F$1:F$1000<>"";ZEILE($1:$1000));ZEILE(F1)))));"";WENN(ZEILE(F1)>ANZAHL2(F:F);"";INDEX(F:F;KKLEINSTE(WENN(F$1:F$1000<>"";ZEILE($1:$1000));ZEILE(F1)))))}$G$1 
H1:H3=WENN(G1="";"";ZEILE())  
{} Matrixformel mit Strg+Umschalt+Enter abschließen
Matrixformeln sind durch geschweifte Klammern {} eingeschlossen
Diese Klammern nicht eingeben!!


definierte Namen  
Name Bezieht sich auf Tabelle Z1S1-Formel
Bereich=Tabelle1!$G$1:INDEX(Tabelle1!$G:$G;MAX(Tabelle1!$H:$H);1) =Tabelle1!R1C7:INDEX(Tabelle1!C7,MAX(Tabelle1!C8),1)

Daten, Gültigkeit  
Zelle Zulassen Daten Wert1 Wert2 Leere Zellen ignorieren Zellendropdown Titel Eingabe-meldung Eingabe-meldung Titel Fehler Fehler-meldung Typ Fehler-meldung Fehler- meldung anzeigen Eingabe- meldung anzeigen
A1Liste   =Bereich     Wahr   Wahr           StoppWahr   Wahr  
B4Liste   =Bereich     Wahr   Wahr           StoppWahr   Wahr  
C7Liste   =Bereich     Wahr   Wahr           StoppWahr   Wahr  
Excel-Inn.de
Hajo-Excel.de
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 25.15 einschl. 64 Bit




GrußformelBeverly's Excel - Inn


  

Betrifft: AW: mit Hilfsspalte und einer benannten Formel ... von: neopa C
Geschrieben am: 26.02.2019 15:43:36

Hallo Rudolf,

... wenn alle max. auszuwählenden Datenwerte in Spalte H stehen, werden in einer (ausblendbaren) Hilfsspalte z.B. Spalte I die noch nicht ausgewählten Datenwerte mit der nach unten ziehend zu kopierenden Formel I1 dynamisch neu gelistet und diese über eine benannte Formel der Datengültigkeit übergeben.

Arbeitsblatt mit dem Namen 'Tabelle1'
 ABCDEFGHI
1       Wahl1Wahl1
2       Wahl2Wahl2
3       Wahl3Wahl4
4 Wahl5     Wahl4Wahl6
5       Wahl5Wahl7
6       Wahl6Wahl8
7  Wahl3    Wahl7 
8       Wahl8 
9         

NameBezug
_Auswahl=Tabelle1!$I$1:INDEX(Tabelle1!$I:$I;ZÄHLENWENN(Tabelle1!$I:$I;"?*"))

ZelleFormel
I1=WENNFEHLER(INDEX(H:H;AGGREGAT(15;6;ZEILE(H$1:H$9)/(ZÄHLENWENN(A$1:C$7;H$1:H$9)=0);ZEILE(H1)))&"";"")

ZelleGültigkeitstypOperatorWert1Wert2
A1Liste =_Auswahl 
B4Liste =_Auswahl 
C7Liste =_Auswahl 
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...


  

Betrifft: AW: mit Hilfsspalte und einer benannten Formel ... von: Rudolf Perkams
Geschrieben am: 26.02.2019 18:56:59

Hallo Werner,
die Lösung spricht mich an.
Ich bekomme jedoch eine Fehlermeldung "Die Quelle muss eine getrennte Liste … sein", wenn ich z.B. in Zelle A1 als Quelle den mit Deiner Formel definierten Namen =_Auswahl eingebe (siehe Beispieldatei).

Wo ist der Wurm?
Gruß Rudolf

https://www.herber.de/bbs/user/127949.xlsx


  

Betrifft: AW: mit Hilfsspalte und einer benannten Formel ... von: Luschi
Geschrieben am: 26.02.2019 21:57:05

Hallo Rudolf ,

Werners Formeln sollte man nicht abschreiben sondern kopieren und dann anpasse; bei Dir fehlen 2 entscheidende Klammern!:
- vor ZÄHLENWENN und
- nach =0
und schon klappt die Lösung.

Gruß von Luschi
aus klein-Paris


Beiträge aus dem Excel-Forum zum Thema "Gültigkeitsliste verringern"