[Excel2013] Erstellen mehrerer abhängiger Dropdownlisten

Khyras Stern · 9. März 2016

Liebe Community,

ich möchte gerne ein Kalkulationsblatt erstellen in dem mehrere Dropdownlisten in gegenseitiger Abhängigkeit ein Ergebnis liefern. Nur habe ich gerade ein ziemlich großes Brett vorm Kopf und bin mit den Kombinationsmöglichkeiten der Formeln überfordert.

Als Hilfeseite habe ich zwar das hier gefunden: http://www.office-hilfe.com/support/showthread.php/8039-Excel-(abh%C3%A4ngige)-DropDown-Men%C3%BCs

Nur fühle ich mich gerade etwas überfordert. Deshalb bräuchte ich eure Hilfe.

Gemäß folgender Tabelle müsste ich eine gegenseitige Abhängigkeit einbringen. Einmal als Übersicht, einmal als Tabelle dargestellt.

Als Ergebnis steht der Wert in Watt aus der Matrix Batteriedaten (in Ah) in Abhängigkeit von der Batteriezeit (in h).
Dazu kommt das das Produkt MS1200 nur die Batterien mit 7,2Ah und 12,0Ah aufnehmen kann, während in die MS1700 auch 24,0Ah verbaut werden können.

Nun müsste ich folgende Abhängigkeiten in eine Tabelle eingeben können:

Auswahl1: Produkt -> MS1200 oder MS1700 -> Dropdownliste 1 (sollte kein Problem darstellen)
Auswahl2: Batteriesatz in Abhängigkeit von Auswahl1
Auswahl3: Batteriezeit
Ergebnis aus der Matrix in Abhängigkeit von Auswahl2 und Auswahl3

Danke für eure Hilfe und Ideen. Ich müsste evtl auch erstmal den Sinn der Formel für den Vergleich verstehen, bzw. wie sollte ich meine Tabellen am besten aufbauen und die Abhängigkeiten richtig darstellen zu können.

Nager47 · 9. März 2016

Hallo Khyras Stern,

Wird die Auswahl 3 (Batterie) auch in Abhängigkeit von Auswahl 2 gewählt?
So wie 2 in Abhängigkeit von 1.

Wenn ja brauchst du für die Drop Down Auswahllisten nur eine Formel (=Indirekt()) und ein paar Namens-defenitionen für Zellbereiche wo deine Auswahlmöglichkeiten reinkommen.

Kann dir das Heute Abend genauer erklären. Habe grade nicht viel Zeit

Grüße Jan

Knufu · 9. März 2016

wie Jan schon kurz beschrieb:
Erste Ebene ist klar.
Zweite Ebene: Erstelle Tabellen die du hier brauchst und benenne den Bereich (Namensmanager) entsprechend der Auswahl aus der ersten Ebene und so weiter.
Nutze dann INDIREKT in der Liste statt einem genauen Zellbezug wie in Ebene 1

Hinweis:
Du wirst deine erste Ebene nicht genau so bezeichnen können, da "MS1200" einem Zellbezug entspricht und diese Bezeichnung ist bereits reserviert! Nutze als zBsp. MS_1200

Anhang anzeigen V2_Blanko_Notlicht_Leistung_eBox_Batteriedaten.xlsx

Khyras Stern · 9. März 2016

Hallo Ralph,
Hallo Jan,

danke für eure Unterstützung. Das mit den Tabellen hatte ich auch schon im Netz gefunden. Aber dort wurde explizit gesagt der Tabellenname müsse genauso heißen wie die Tabellenüberschrift. Ging bei mir aber nicht wegen Namenskonflikt.

Jan, wäre toll wenn du mir da nochmal helfen würdest. Alternativ habe ich schon überlegt die Auswahl einzuschränken:

MS1200/7,2Ah
MS1200/12,0Ah
MS1700/...

und dann nur noch die Batterieüberbrückungszeit auswählen:

8h
5h
3h
...

somit wäre es dann nur noch eine Matrix-Rechnung, richtig?

Aber das mit den Tabellen und INDIREKT würde mich schon interessieren, hatte bei mir nicht geklappt. Weiß aber nicht ob es daran liegt das die Zellen der Zellbezüge über mehrere Zellen gehen (Beschriftung MS1200 geht über 3-4 Zellen). Ich hatte dann als Zellbezug die erste Zelle genommen.

Kein Stress, ich werde da sowieso bis tief in die Nacht sitzen und probieren.

Jan.Schroeder schrieb:
Hallo Khyras Stern,

Wird die Auswahl 3 (Batterie) auch in Abhängigkeit von Auswahl 2 gewählt?
So wie 2 in Abhängigkeit von 1.

Wenn ja brauchst du für die Drop Down Auswahllisten nur eine Formel (=Indirekt()) und ein paar Namens-defenitionen für Zellbereiche wo deine Auswahlmöglichkeiten reinkommen.

Kann dir das Heute Abend genauer erklären. Habe grade nicht viel Zeit

Grüße Jan

Nein, die Auswahl 3 ist unabhängig von 1 oder 2. Nur das Ergebnis ist abhängig von 2+3.

@Ralph: Deine Liste habe ich mal versucht umzuschreiben auf die erforderlichen Werte. Ebene 2.1 und 2.2 konnte ich noch erfassen, Ebene 3 wollte der Namensmanager nicht die 7,2Ah. Ebene 4 ist nicht auswählbar sondern das Ergebnis aus 2+3.

Knufu · 9. März 2016

Khyras Stern schrieb:
Aber das mit den Tabellen und INDIREKT würde mich schon interessieren, hatte bei mir nicht geklappt.

Hattest du dir mal meine Beispieltabelle angeschaut? Dort hatte ich es mal begonnen mit Indirekt?!

Khyras Stern · 9. März 2016

Knufu schrieb:
Hattest du dir mal meine Beispieltabelle angeschaut? Dort hatte ich es mal begonnen mit Indirekt?!

Ja, habe ich doch oben geschrieben. Hatte mir deine Tabelle erst nach meinem Posting angeschaut. Ich muss es mal komplett neu probieren, aber habe von dir ja schon mal die Vorlage um es besser zu verstehen.

Danke!

Schön, meine Matrix habe ich fertig, aber was mache ich jetzt? Wie bekomme ich in der Ergebniszelle den Wert meiner Matrix-Zelle an Hand der Abhängigkeit Auswahl 2+3, bzw. habe ich das jetzt anders aufgebaut. Ich weiß nur nicht, wie ich den Wert zurückgebe bzw. den Zellbezug zur Auswahl herstelle. Ich bin echt zu blöd.

Wenn ihr eine Lösung habt um Auswahl 1, 2 und 3 zu kombinieren ist auch ok. So habe ich jetzt nur 2x Daten auszuwählen. Ich dachte das wäre einfacher, aber ich verstehe es dennoch nicht.

Nager47 · 9. März 2016

Hallo Khyras Stern,

die letzte Tabelle die du hochgeladen hast sieht doch schon mal ganz gut aus

Um deine Auswahl aus der Matrix rauszubekommen brauchst du nur noch die Index Formel mit zwei Sverweisen.
Ich hab dir das mal in die Liste eingefügt. Glaube das müsste so Stimmen

(Das ganze müsste natürlich noch "Schön" gemacht werden.)

Allerdings verstehe ich jetzt nicht welche Dropdown Listen voneinander abhänging gemacht werden sollen

Grüße Jan

Khyras Stern · 9. März 2016

Danke Jan,

ich habe derweil auch etwas probiert und es jetzt mit =INDEX(BB100:BH105;VERGLEICH(Z3;BB100:BB105;0);VERGLEICH(AF3;BB100:BH100;0)) hinbekommen, wobei ich die Matrix auf das 1. Tabellenblatt holen musste, weil sonst die Verknüpfung nicht passte. Jetzt schaue ich mir mal deine Excel an.

Gruß Jens

Hinsichtlich der Dropdownlisten sollte es ursprünglich so aussehen.

Auswahl 1 -> Wenn MS1200 -> dann Auswahl 2 -> nur 7,2Ah und 12,0Ah -> bei Auswahl 3 -> Batt.überbrückungszeit 0,5-8h zeige max. Leistung an.

Jetzt habe ich das etwas eingeschränkt und die Auswahl 1+2 zusammengefasst. So daß nur noch die Station inkl. Batterie ausgewählt werden muss und dann entschieden wird, für wie lange die Batterien vorhalten müssen. Daraus ergibt sich dann die mögliche Leistung der Station. Habe mal ein Bild des relevanten Excelbereiches hochgeladen.

Vorteil von SVERWEIS gegenüber VERWEIS?

Ergänzung (10. März 2016)

Na toll. In der Arbeitsmappe waren auch Verknüpfungen auf die Zellen die ich gerade abgeändert habe. Nachdem ich diese Verknüpfungen anpassen wollte, meldete mir Excel das es beim Speichern Probleme gäbe.

GN8

Ergänzung (10. März 2016)

Guten Morgen Jan,

was bedeuten die Hilfsnummern in der Spalte B bei deiner Excel-Liste? Bei den stunden hast du nur 1,2,3,3,3,3 zu stehen.

Janush · 10. März 2016

Hallo,

ich habe das ganze mal als overkill Loesung mit programmierbaren ComboBoxen gebaut. Ich war noch nie Freund von komplizierten Formeln

Fuer ganz mutige als .xlsm download angehaengt

Fuer nicht ganz so mutige, hier noch der Code. Alles sehr basic, aber das Potential ist fast endlos. Damit der Code funktioniert, brauchts nur 3 ActiveX comboboxen mit den folgenden Namen.

TypAuswahl
Batterie
Zeit

Code:

Const StartColZeit = 3
Const StartColTyp = 11

Private Sub TypAuswahl_DropButtonClick()
Dim col As Integer
col = StartColTyp
With TypAuswahl
    Do Until .ListCount = 0
        .RemoveItem (0)
    Loop
    Do Until Cells(2, col) = ""
        .AddItem Cells(2, col)
        col = col + 1
    Loop
End With
End Sub

Private Sub TypAuswahl_Change()
Dim col As Integer
Dim row As Integer

col = StartColTyp
row = 3

With Batterie
    .Clear
    Do Until Cells(2, col) = "" Or Cells(2, col) = TypAuswahl.Value
        col = col + 1
    Loop
    Do Until Cells(row, col) = ""
        .AddItem Cells(row, col)
        row = row + 1
    Loop
End With
End Sub

Private Sub Batterie_Change()
Dim col As Integer

col = StartColZeit

With Zeit
    .Clear
    Do Until Cells(3, col) = ""
        .AddItem Cells(3, col)
        col = col + 1
    Loop
End With
End Sub

Private Sub Zeit_Change()
If Not TypAuswahl.Value = "" And Not Batterie.Value = "" And Not Zeit.Value = "" Then
    Cells(11, 2) = Cells(Batterie.ListIndex + 4, Zeit.ListIndex + 3)
Else
    Cells(11, 2) = ""
End If
End Sub

Gruss
Martin

Khyras Stern · 10. März 2016

Hallo Martin,

coole Sache. Wobei mich dann VBA-Scrpte völlig überfordern. Anderseits kann ich das bei dem von mir verwendeten Arbeitsblatt nicht einbauen, da die Arbeitsmappe aus 5x Tabellenblättern besteht, wobei im ersten Tabellenblatt die Auswahl erfolgt und in den vier anderen die Auswahlkriterien ebenfalls übernommen und angezeigt werden. Mit der Toolbox kann ich nur das Ergebnis auswerten.

Dennoch danke für deine Mühe und die tolle Idee.

Schöne Grüße
Jens

Janush · 10. März 2016

Hi Jens,

keine Muehe sondern eher ein Hobby

Wenn du da wirklich was ernsthaftes bauen willst, was auch stabil fuer immer laeuft, kommst du meiner Meinung nach nicht um nen schoenen VBA Code herum. Ich weiss das sehen hier nicht alle so

Und keine Angst, VBA kommt auch mit 5 TabellenBlaettern klar. Wenn Du an ner alternativen Loesung interessiert bist, lasses mich wissen.

Khyras Stern · 10. März 2016

So, geschafft. Habe heute dann alle Tabellen neu angelegt und die Formeln eingepflegt. Klappt Super. Da ich eh alles neu machen musste, und gestern gemerkt habe das die Abhängigkeiten doch nicht so schlimm sind, wurden es dann doch 3x Dropdownlisten.

Liste 1: Auswahl der Station
Liste 2: Auswahl des passenden Akkus in Abhängigkeit von Auswahl der Station
Liste 3: Wahl der Überbrückungszeit (diese ist ja unabhängig von Liste 1 und 2)
Ergebnis erscheint dann in Abhängigkeit von Auswahl 2 und 3 anhand der Leistungsmatrix.

Ich danke euch allen für eure Tipps und Hilfe!

@Martin:
Die Tabellenblätter 2-4 zeigen nur die erfolgte Auswahl aus Tabellenblatt 1 nochmals an. Da ich die verknüpften Zellen neu formatiert hatte, passte das alles nicht mehr. Ich habe den Bezug jetzt über =VERKETTEN gelöst.

Hier noch die Formeln:

Dropdownlisten:
=BEREICH.VERSCHIEBEN(Tabelle1!$BB$81;;;ANZAHL2(Tabelle1!$BB:$BB)-1)
=BEREICH.VERSCHIEBEN(Tabelle1!$BC$81;;;ANZAHL2(Tabelle1!$BC:$BC)-1)
='e-Box x - SUB1_V.1.x'!$BD$78:$BJ$78

Datenüberprüfung:
=INDEX($BB$81:$BC$84;;VERGLEICH($Z3;$BB$80:$BC$80; ))
=_8h

Wobei _8h die Dropdownliste 3 ist. Excel zieht sich da gerne mal den Namen.

Ergebnis:
=INDEX(BD81:BJ83;VERGLEICH(AD3;BC81:BC83;0);VERGLEICH(AF3;BD78:BJ78;0))

Janush · 10. März 2016

Sieht doch gut aus. Wenn ich mal ne Formel brauchen schau ich nochmal bei dir vorbei. Ohne mist,versteh nur Bahnhof

Was ist eigentlich das Ziel der Übung? Kannst ja mal das File hochladen.

Nager47 · 10. März 2016

Khyras Stern schrieb:
Guten Morgen Jan,

was bedeuten die Hilfsnummern in der Spalte B bei deiner Excel-Liste? Bei den stunden hast du nur 1,2,3,3,3,3 zu stehen.

oh ja da müsste dann auch 1,2,3,4,5,6,7 stehen. Kleiner Fehler!

Die Hilfsnummern waren dazu da damit die Indirekt Formel weiß in welcher Zeile und Spalte sie das gewünschte ergebniss raussuchen muss.
Im prinzip weiß die Formel dadurch nur dass z.B. "5h" in der zweiten Spalte ist. Und wenn du jetzt in deiner Drop Down "5h" angeklikt hast, dann hat der dir das Ergebniss aus der zweiten Spalte rausgesucht.
Und bei der Zeile dann genau so.

Aber du hast ja jetzt schon ein Lösungsweg gefunden

Khyras Stern · 10. März 2016

Das File selber kann ich leider nicht hochladen, da es nur firmenintern genutzt wird und ich es nicht veröffentlichen darf. Außerdem habe ich da nur einen geringen Teil zu beigetragen. das Ziel war es die Daten nicht immer händisch eintragen zu müssen und evtl. dabei das ein oder andere zu vergessen (ich bin da so ein Kandidat). Bisher musste man immer an Hand der Handeingabe aus der Liste die Leistung in die dazugehörige Zelle eintragen, dieses dient dann der Leistungskalkulation in allen fünf Tabellenblättern.

@Jan: Ja, so hat mein Kollege das auch in anderen Tabellenbereichen gehandhabt habe ich heute gesehen..

@Martin: HaHa, ich glaube nicht das du Formeln brauchst

Suche

[Excel2013] Erstellen mehrerer abhängiger Dropdownlisten

Khyras Stern

Gast

Anhänge

Nager47

Cadet 4th Year

Knufu

Lieutenant

Khyras Stern

Gast

Knufu

Lieutenant

Khyras Stern

Gast

Anhänge

Nager47

Cadet 4th Year

Khyras Stern

Gast

Anhänge

Janush

Lieutenant

Anhänge

Khyras Stern

Gast

Janush

Lieutenant

Khyras Stern

Gast

Anhänge

Janush

Lieutenant

Nager47

Cadet 4th Year

Khyras Stern

Gast