Office: (Office 2013) Excel Dropdown mit abhängigen Werten

Hallo liebes Forum,
über eine Google-Recherche und die einschlägigen Excel-Forum bin ich in meiner Problemstellung leider nicht weiter gekommen.

Es geht um Folgendes:
Ich habe eine Tabelle (sagen wir mal Tabelle 1), in der zu möglichen ID's (z.B. Mitarbeiternummern) verschiedene zugehörige Aufträge möglich sind.

In einer weiteren Tabelle (sagen wir mal Tabelle 2), die der Zeiterfassung dient, sollen nach Eingabe der Mitarbeiternummer (über Sverweis und Dropdown gelöst) in einer Spalte Auftrag im Rahmen eines Dropdowns alle möglichen Aufträge für den jeweiligen Mitarbeiter angezeigt werden.

Leider klappt das mit den mir bekannten bzw. eingesetzten Möglichkeiten nicht wirklich.
Habe bereits mit Index und Vergleich Versuche unternommen, scheitere aber bislang IMMER. In Verbindung mit Index und Vergleich sowie KKleinste wird mir immer nur 1 Wert zur Auswahl im Dropdown angeboten.

Zur Erläuterung habe ich ein einfaches Beispiel angehängt, aus dem hoffentlich hervorgeht, was ich mir vorstelle.
Ich würde mich echt freuen, wenn es eine Lösung hierfür gäbe, weil ich schon viel Zeit zur Recherche und zum Ausprobieren verbraten habe, aber nicht wirklich schlauer geworden bin.

VBA bzw. ein Kombinationsfeld scheidet aus, da ich mir sonst die zahlreichen Auswertungen (Pivot, Abfragen etc.) zerhauen würde.

Vielen Dank und viele Grüße
Michael

 

Hola,

eine richtige Beispieldatei (xlsx Format) wäre sinnvoll.

Gruß,
steve1da

 

Hallo, oder hola,
klar ist eine Excel-Datei anschaulicher. Daher habe ich anbei eine "abgespeckte" Excel-Datei angefügt, aus der man hoffentlich erkennen kann, was gemeint ist.

Vielen Dank und viele Grüße
Michael

Den Anhang Mappe2.xlsx betrachten

 

Hola,

nur damit keine Mißverständnisse aufkommen - wo sollen die Dropdowns genau erscheinen?
Ein konkretes Beispiel zu einer ID wäre von Vorteil.

Gruß,
steve1da

 

Hallo, vielen Dank für die schnelle Antwort.

Die DropDowns sollen in der "Tabelle 2" in der "Spalte G" angezeigt werden, so dass für den Mitarbeiter die ihm zugeordneten Aufträge ausgewählt werden können.
Mit meinen beschränkten Möglichkeiten habe ich bislang leider maximal einen Eintrag im Dropdown hinbekommen.
(z.B. G21. Hier wird der Auftrag 2015-000001 angezeigt, der dem Mitarbeiter 1 (ID 12345) zugeordnet ist.

Beim Blick in die "Tabelle 1" wird jedoch ersichtlich, dass dem Mitarbeiter folgende Aufträge zugeteilt wurden:
- 2015-000001
- 2014-546548
- 2011-485483

Im DropDown wird aber nur 2015-000001 angezeigt (1. Wert). Die 2 anderen "gehen unter".

Bei allen Recherchen bin ich nur auf die Möglichkeit gekommen, dass es sich um "Suchfelder" handelt. Nach Eingabe des Suchbegriffs würde dann in meinem Beispiel in der "Tabelle 1" eine Hilfstabelle eingefügt, die sich mittels Index etc. befüllt.
Hier habe ich aber kein "einzelnes Suchfeld", sondern jede Zeile mit dem individuellen Mitarbeiter stellt quasi ein eigenes Suchfeld dar.

Habe gerade noch einen Fehler in der Excel-Tabelle gefunden und daher die Mappe nochmal angefügt.

Danke für die Mühe, sich den Sachverhalt anzuschauen.
Gruß
Michael

Den Anhang Mappe2 (V2).xlsx betrachten

 

Hola,

die ID in Spalte F kannst du ohne großartiges Hilfskonstrukt in Tabelle1 auslesen:

=WENNFEHLER(INDEX('Tabelle 1'!$B$5:$B$13;VERGLEICH(D5;MA_Bezeichnung;0));"")

Zum abhängigen Dopdown kann ich noch nichts sagen, da ich noch daran bastel.

Gruß,
steve1da

 

Moinsens!

Irgendwie sind deine ID vergniesgnaddelt. Sind das jetzt in beiden Tabellen ID -> MA oder sind das unterschiedliche ID? So ganz passen Tabelle 1 und Tabelle 2 nicht zusammen. In Tabelle 1 hast Du 3 verschiedene ID für MA1, in Tabelle 2 benutzt Du davon aber nur die 1. aus der Liste.

Der Aufbau der Daten ist alles in allem eh etwas unglücklich. Genau genommen müßtest du eine Tabelle "MA" (nur "IDm" und "Name") und eine separate Tabelle "Aufträge" (nur "IDa" und "Bezeichnung") aufbauen. Dazu kommt dann eine weitere Tabelle "Mitarbeiter / Aufträge" in der sich dann "IDm" und "IDa" befinden, jeweils ggf. aus einem Dropdown gefüllt. Alle weiteren, benötigten Details lassen sich dann über die ID und sVerweis() aus den ersten beiden Tabellen holen.

Das wäre dann eine "n:m" Beziehung, denn eine IDa kann mehrmals in der Liste stehen und dabei unterschiedliche IDm zugewiesen bekommen --> 1 Mitarbeiter kann m Aufträge bearbeiten, aber 1 Auftrag kann auch von n Mitarbeitern bearbeitet werden = n Mitarbeiter können m Aufträge bearbeiten. Das weitere Verarbeiten der Daten ist dann in Excel aber ziemlich aufwändig und eher ein Job für Access, also ein Datenbank-System. Oder man stellt sich auf Bandwurm-Formeln und Hilfstabellen auf Extra-Blättern ein.

Wenn immer nur genau 1 MA an einem Auftrag beteiligt sein kann, kann man das ganze etwas verkürzen und eine "1:n" Beziehung aufbauen --> 1 Mitarbeiter kann n Aufträge bearbeiten, aber 1 Auftrag immer nur von 1 Mitarbeiter. Das ist zwar auch noch recht aufwändig für Excel, weil es mit dem sVerweis() eigentlich nur für "1:1" Beziehungen ausgelegt ist. Aber da geht noch was.

Dann hast Du Deine Tabelle "MA" (mit "IDm" und "Name") und Deine Tabelle "Aufträge" (mit "IDa" und "Bezeichnung" sowie dem zusätzlichen Feld "IDm"). Die 3. Tabelle brauchst Du dann nicht mehr. Der MA ist über IDm direkt dem Auftrag zugeordnet.

Diese beiden (bzw. ggf. drei) Tabellen wären dann die Datenerfassung. Was Du jetzt aber eigentlich fragst, ist schon eher die Ausgabe / Auswertung der erfassten Daten.

Du wirst eine Hilfstabelle brauchen, aus der dann das Dropdown gefüllt werden kann. Dazu kann Dir wahrscheinlich http://www.excelformeln.de/formeln.html?welcher=28 weiterhelfen, weil Du "mehr als einen Rückgabewert aus einem sVerweis()" brauchst.

Ich schätze aber, daß Du ohne VBA hier auch nicht mehr sehr viel weiter kommst. Das Dropdown (Auswahl der passenden IDm zur gewählten IDa oder umgekehrt) muß dann ja in jeder Zeile eine andere Datenquelle erhalten. Und wenn sich die ID ändert, muß die zugehörige Liste auch neu aufgebaut werden. Fällt mir so auf Anhieb keine reine Formel-Lösung ein...

Gruß, Michael

 

Hi,
vielen Dank für den Tipp.
Die Formel Wennfehler war mir noch unbekannt, obwohl ich glaub ich schon viele Formeln kenne.
Habe immer mit Wenn(istfehler(b);a;b) rumhantiert. Das macht die Sache doch schon wesentlich übersichtlicher.

Die Hilfsspalte habe ich deswegen eingeführt, damit die Mitarbeiter im DropDown (Spalte D) in alphabetischer Reihenfolge sind.
Da die Tabelle 1 "lebt" und weiter gefüllt wird mit den entsprechenden Aufträgen ist die Tabelle chronologisch und nicht alphabetisch.

Eine andere Möglichkeit, alphabetisch sortierte Reihenfolgen im Dropdown darzustellen, ist mir bislang leider nicht über den Weg gelaufen.

Vielen Dank für Deine Mühen.
Wenn das mit dem Dropdown funktioniert, dann bist Du mein Held.

Gruß
Michael

 

Hola,

wenn du die Darstellung in Tabelle1 etwas veränderst, ginge das wie hier beschrieben:

http://www.office-hilfe.com/support/showthread.php/8039-Excel-(abhängige)-DropDown-Menüs

Dazu musst du die Mitarbeiter 1-6 nebeneinander schreiben, z.B. in B19 G19, in B20 dann als Matrixformel (mit Strg-Shift-Enter abschließen):

Code:

=WENNFEHLER(INDEX(Auftrag_Nummer;KKLEINSTE(WENN(MA_Bezeichnung=B$19;ZEILE($5:$13)-4);ZEILE(A1)));"")

Gruß,
steve1da

 

Hallo steve1da,
vielen Dank für die Antwort. Auf die Idee bin ich auch bereits gekommen, würde mir dann aber das Konstrukt der Auswertungen zerschießen, da ich im Rahmen von Pivot-Tabellen und Abfragen auf die einzelnen Monate und die "Tabelle 1" zugreife.

Vielleicht gibt es noch eine andere Möglichkeit?

Viele Grüße
Michael

 

Hola,

das kannst du doch immer noch.

Ich habe lediglich aus den vorhandenen Daten eine weitere Darstellung erzeugt, die ursprüngliche bleibt bestehen.
Diese weitere Darstellung passt sich auch an, wenn du in der ursprünglichen etwas änderst.

Gruß,
steve1da

 

Hola,
das ist natürlich eine super Idee!
Das probiere ich aus und berichte, ob es den gewünschten Erfolg gebracht hat.
Vielen Dank!

Gruß
Michael