Live-Forum - Die aktuellen Beiträge
Datum
Titel
13.05.2024 21:39:28
Zeichen in einem String austauschen
13.05.2024 20:51:32
Einkaufsliste - Automatischer Abgleich Arbeitsblätter
13.05.2024 16:26:07
Excel Tabelle auswerten - Problem mit dynamischer Spalte
13.05.2024 15:54:42
Userform per doubleclick event öffnen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1804to1808
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1804to1808
1804to1808
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Abhängige Gültigkeit

Abhängige Gültigkeit
13.01.2021 16:47:39
Theo
Hallo Zusammen,
ich befürchte mein Problem ist schon 1000 mal hier behandelt worden, aber irgendwie finde ich nicht ganz das passende.
Es geht darum dass ich in der Parameter Spalte (D) eine Gültigkeitsprüfung (Data validation) machen möchte, die abhängig von den Werten in Spalte B und C ist. Die mögichen Werte sind auf einem anderen Arbeitsblatt (picklist) vorhanden. Normalerweise würde ich das ganz einfach in VBA machen, Blöderweise soll das zukünftig aber aus einem Prozess heraus generiert werden, der zwar Excel Gültigkeit, aber kein VBA unterstützt.
Ich habe eine stark vereinfachte Beispiel Datei angehängt, Die Gültigkeitswerte in Zelle D2 und D3 habe ich zur Veranschaulichung Hardgecoded.
https://www.herber.de/bbs/user/142981.xlsx
Es wäre toll, wenn jemand von Euch eine Idee hätte!
Theo

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: mit Hilfe von INDEX() und VERGLEICH() und ...
13.01.2021 17:07:58
INDEX()
Hallo Theo,
... der Definition einer benannten Formel
Bei aktivierter Zelle D2 definiere Im Namensmanager folgende Formel:
=INDEX(INDEX(Picklist!C:C;VERGLEICH(Daten!B2;Picklist!A:A;0)):INDEX(Picklist!C:C;VERGLEICH(Daten!B2;Picklist!A:A;0)+ZÄHLENWENNS(Picklist!A:A;Daten!B2;Picklist!B:B;Daten!C2)-1);)
und Vergib dieser den Namen z.B.: _Auswahl
In der Datenüberprüfung definierst Du in der Quelle für Liste für D2:D15 dann: =_Auswahl
Gruß Werner
.. , - ...
AW: mit Hilfe von INDEX() und VERGLEICH() und ...
13.01.2021 18:24:04
INDEX()
Hallo Theo,
damit Werners Vorschlag so richtig Freude macht, solltest Du den Namen '_Auswahl' mit festen Spaltenbezügen definieren:
=INDEX(INDEX(Picklist!$C:$C;VERGLEICH(Daten!$B2;Picklist!$A:$A;0)):INDEX(Picklist!$C:$C; VERGLEICH(Daten!$B2;Picklist!$A:$A;0)+ZÄHLENWENNS(Picklist!$A:$A;Daten!$B2;Picklist!$B:$B;Daten!$C2) -1);)
Gruß von Luschi
aus klein-Paris
Anzeige
AW: dies ist so nicht notwendig ...
13.01.2021 20:09:47
neopa
Hallo Luschi,
... wenn die benannte Formel wie von mir geschrieben bei aktiver Zelle D2 definiert wird.
Die von Dir angegebene Formel kann zwar Spaltenunabhängig definiert werden, muss aber in Zeile 2 definiert werden.
Gruß Werner
.. , - ...
AW: dies ist so nicht notwendig ...
13.01.2021 20:34:50
Luschi
Hallo Werner,
und genau das ist das Problem, man muß die Zelle aktivieren, in der die Datengültigkeit dann definiert wird, um den Namen zu definieren.
Da ich aber per Vba Datentabellen aus Tab-Vorlagen neu definiere und so auch die erzeugten Namen übernehmen will, sind solcher händischen Voraussetzungen kontraproduktiv und laufen dann ins Leere.
Aber lassen wir uns überraschen, ob der Anfrager mit diesem Lösungsvorschlag etwas anfangen kann.
Gruß von Luschi
aus klein-Paris
Anzeige
AW: dies ist so nicht notwendig ...
14.01.2021 11:00:37
Theo
Hallo Werner, hallo Luschi,
vielen Dank schonmal. Ich habe beide Versionen ausprobiert und angehängt:
https://www.herber.de/bbs/user/142999.xlsx
Leider liefern beide Versionen nicht das richtige Ergebnis.
Beispiel:
Für die Kombination US / CN sollten laut Pickliste nur folgende Kombination in der Gültigkeitsliste auftauchen:
USCN1 - USCN3 - USCN5
Tatsächlich werden aber folgende Kombinationen vorgeschlagen:
USAR1 - USBZ1 - USCN1
Es sieht für mich so aus, dass alle Werte angezeigt werden bei der die erste Bedingung zutrifft, solange bis die zweite Bedingung ebenfalls zurtrifft (statt erst hier zu starten).
Da meine Beispieldatei stark vereinfacht ist, hat die Version von Luschi einen entscheidenden Vorteil:
Die Validierung funktioniert auch (so sie dann funktioniert) wenn sich die Spalten verschieben.
In meiner "echten" Datei werden die Spalten PlantCountry /VendorCountry und Parameter auf dem Daten worksheet, statt in Spalten B / C /D vorausichtlich in Spalten G / J und AM sein.
Es wäre toll, wenn Ihr nochmal nachschauen könntet!
Danke
Theo
Anzeige
AW: wenn Spalten verschoben werden können ...
14.01.2021 12:04:26
neopa
Hallo Theo,
... dann ist es natürlich sinnvoll, in der Formel die Spalten zu "verabsolutieren".
Davon war ich nicht ausgegangen, genauso wenig war ich davon ausgegangen dass Zeilen verschoben werden können. Bei letzteren wird die Formellösung wesentlich komplizierten. Auch deswegen wäre es sinnvoll mit formatierten Tabellen zu arbeiten und die Formeln dafür dann zu definieren.
Ausgegangen bin ich auch davon, dass die Daten in "Picklist" stets sortiert sind zuerst nach Plant dann nach Vendor.
Recht hast Du damit, dass ich noch nicht den jeweiligen 1. Datenwert richtig ermittelt habe.
Meine neue Formel für die benannte Formel Taxcode (die Spaltenverschiebung erlaubt) lautet nunmehr (definiert in D2:

=INDEX(INDEX(Picklist!$C:$C;AGGREGAT(15;6;ZEILE(Daten!B$2:B$99)/(Daten!$B2=Picklist!$A$2:$A$99) /(Daten!$C2=Picklist!$B$2:$B$99);1)):INDEX(Picklist!$C:$C;AGGREGAT(15;6;ZEILE(Daten!B$2:B$99) /(Daten!$B2=Picklist!$A$2:$A$99)/(Daten!$C2=Picklist!$B$2:$B$99);1)+ZÄHLENWENNS(Picklist!$A:$A; Daten!$B2;Picklist!$B:$B;Daten!$C2)-1);)

Gruß Werner
.. , - ...
Anzeige
AW: wenn Spalten verschoben werden können ...
14.01.2021 14:19:11
Theo
Hallo Werner,
vielen Dank, jetzt funktioniert es wunderbar! Ich habe nur eine Änderung vorgenomen, statt den Bereichen zwischen Zeile 2 und 99 referenziere ich jetzt die gesamte Spalte. Hintergrund dafür ist, dass meine Pickliste in Wirklichkeit erheblich mehr als 99 Einträge haben wird.
=INDEX(INDEX(Picklist!$C:$C;AGGREGAT(15;6;ZEILE(Daten!C:C)/(Daten!$B2=Picklist!$A:$A) /(Daten!$C2=Picklist!$B:$B);1)):INDEX(Picklist!$C:$C;AGGREGAT(15;6;ZEILE(Daten!C:C) /(Daten!$B2=Picklist!$A:$A)/(Daten!$C2=Picklist!$B:$B);1)+ZÄHLENWENNS(Picklist!$A:$A; Daten!$B2; Picklist!$B:$B;Daten!$C2)-1);)
Anzeige
AW: zu Deiner Ändeung würde ich nicht raten, ...
14.01.2021 16:40:54
neopa
Hallo Theo,
... da es bei derartigen Formeln angeraten ist, den Auswertungsbereich auf den max zu erwartbar auszuwertenden Bereich zu beschränken um Rechenressourcen zu sparen.
Übrigens: Bei einer als Tabelle formatierten Liste wird automatisch immer der wirklich auszuwertenden Bereich genommen, wenn die Formeln dafür definiert wurde.
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige