Excel Auswahlliste

[Eisb_r13]

Hallo,

ich habe Excel 2007 und folgendes Problem:

Ich habe in einer Zelle z.B. A2 im Tabellenblatt 1 eine Auswahlliste erstellt, diese Liste befindet sich im Tabellenblatt2 und enthält z.B. 15 verschiedene Namen.
Jetzt möchte ich gern in Zelle A3 eine weitere Auswahlliste haben die aber den in der Zelle A2 gewählten Namen automatisch weglässt.

Ich möchte praktisch einen Schichtplan erstellen, bei dem ich die Namen der Leute die in Früh und Spätschicht arbeiten sollen nur anklicken brauche, da aber keiner eine Doppelschicht machen soll muss ein schonmal angewählter Namen aus der Liste verschwinden.

Ich hoffe Ihr habt einen Rat für mich

Danke

[Reinhard_fb8cb7]

Ich habe in einer Zelle z.B. A2 im Tabellenblatt 1 eine
Auswahlliste erstellt, diese Liste befindet sich im
Tabellenblatt2 und enthält z.B. 15 verschiedene Namen.
Jetzt möchte ich gern in Zelle A3 eine weitere Auswahlliste
haben die aber den in der Zelle A2 gewählten Namen automatisch
weglässt.

Hi Eisbär,

so z.B.

Tabellenblatt: [Mappe1]!Tabelle2
 │ A │ B │
───┼───┼───┤
 1 │ A │ A │
───┼───┼───┤
 2 │ B │ B │
───┼───┼───┤
 3 │ C │ D │
───┼───┼───┤
 4 │ D │ E │
───┼───┼───┤
 5 │ E │ F │
───┼───┼───┤
 6 │ F │ G │
───┼───┼───┤
 7 │ G │ H │
───┼───┼───┤
 8 │ H │ I │
───┼───┼───┤
 9 │ I │ J │
───┼───┼───┤
10 │ J │ K │
───┼───┼───┤
11 │ K │ L │
───┼───┼───┤
12 │ L │ M │
───┼───┼───┤
13 │ M │ N │
───┼───┼───┤
14 │ N │ O │
───┼───┼───┤
15 │ O │ │
───┴───┴───┘
Benutzte Formeln:
B1 : =WENN(WENN(ZÄHLENWENN($A$1:A1;Tabelle1!$A$2)=0;A1;A2)=0;"";WENN(ZÄHLENWENN($A$1:A1;Tabelle1!$A$2)=0;A1;A2))
B2 : =WENN(WENN(ZÄHLENWENN($A$1:A2;Tabelle1!$A$2)=0;A2;A3)=0;"";WENN(ZÄHLENWENN($A$1:A2;Tabelle1!$A$2)=0;A2;A3))
B3 : =WENN(WENN(ZÄHLENWENN($A$1:A3;Tabelle1!$A$2)=0;A3;A4)=0;"";WENN(ZÄHLENWENN($A$1:A3;Tabelle1!$A$2)=0;A3;A4))
usw. bis B15

Festgelegte Namen:
Alle: =Tabelle2!$A$1:blush:A$15, unbenutzt in Selektion.
Teil: =Tabelle2!$B$1:blush:B$15, unbenutzt in Selektion.

A1:B15
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

[Eisb_r13]

Hallo Reinhard,

vielen Dank für Deine schnelle Antwort.
habe die ganze Sache aber leider nicht ganz verstanden, ich schicke Dir mal ein Excelblatt vielleicht kann man es daran besser erkenn was mein Problem ist.
Ich hoffe das ist gestattet??

MfG Sven

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

[Reinhard_fb8cb7]

habe die ganze Sache aber leider nicht ganz verstanden, ich
schicke Dir mal ein Excelblatt vielleicht kann man es daran
besser erkenn was mein Problem ist.
Ich hoffe das ist gestattet??

Moin Sven,

dafür ist FAQ:2861 besser geeignet.

Schreibe in alle Zellen von Schichtplan!C4:G6 und Schichtplan!C8:G13 die Gültigkeitsformel
=Verf

Für die Kalenderwochenzelle:
=KW

Wenn der Dropdownpfeil nicht kommt, so lösche das Blatt Schichtplan und erstelle es neu, rgendwas verhinderte bei mir daß der Dropdown-Pfeil in den Zellen erscheint.

Hier ist die Datei wo das auftritt, dort heißt das Originalblatt nun „Seltsam“:
http://www.hostarea.de/server-01/Januar-30bc0d6d7a.xls

Erstelle ein Hilfsblatt „Hilf“, schreibe in E1 „KW 1“ und ziehe die rechte untere Ecke von E1 bis runter zu E53 mit der Maus.

Tabellenblatt: H:\[Schichttest.xls]!Hilf
 │ A │ B │ C │ D │ E │
───┼──────────┼───────────┼────┼─────┼───────┤
 1 │ Alle Aks │ Verfügbar │ │ │ KW 1 │
───┼──────────┼───────────┼────┼─────┼───────┤
 2 │ AK1 │ AK3 │ │ │ KW 2 │
───┼──────────┼───────────┼────┼─────┼───────┤
 3 │ AK2 │ AK8 │ │ │ KW 3 │
───┼──────────┼───────────┼────┼─────┼───────┤
 4 │ AK3 │ AK9 │ 4 │ AK3 │ KW 4 │
───┼──────────┼───────────┼────┼─────┼───────┤
 5 │ AK4 │ AK10 │ │ │ KW 5 │
───┼──────────┼───────────┼────┼─────┼───────┤
 6 │ AK5 │ AK11 │ │ │ KW 6 │
───┼──────────┼───────────┼────┼─────┼───────┤
 7 │ AK6 │ AK12 │ │ │ KW 7 │
───┼──────────┼───────────┼────┼─────┼───────┤
 8 │ AK7 │ AK13 │ │ │ KW 8 │
───┼──────────┼───────────┼────┼─────┼───────┤
 9 │ AK8 │ AK14 │ 9 │ AK8 │ KW 9 │
───┼──────────┼───────────┼────┼─────┼───────┤
10 │ AK9 │ AK15 │ 10 │ AK9 │ KW 10 │
───┴──────────┴───────────┴────┴─────┴───────┘
Benutzte Formeln:
B2 : =WENN(ISTFEHLER(SVERWEIS(KKLEINSTE(C:C;ZEILE()-1);C:smiley:;2;0));"";SVERWEIS(KKLEINSTE(C:C;ZEILE()-1);C:smiley:;2;0))
B3 : =WENN(ISTFEHLER(SVERWEIS(KKLEINSTE(C:C;ZEILE()-1);C:smiley:;2;0));"";SVERWEIS(KKLEINSTE(C:C;ZEILE()-1);C:smiley:;2;0))
B4 : =WENN(ISTFEHLER(SVERWEIS(KKLEINSTE(C:C;ZEILE()-1);C:smiley:;2;0));"";SVERWEIS(KKLEINSTE(C:C;ZEILE()-1);C:smiley:;2;0))
usw. in B nach unten kopieren.

C2 : =WENN(D2"";ZEILE();"")
C3 : =WENN(D3"";ZEILE();"")
C4 : =WENN(D4"";ZEILE();"")
usw. in B nach unten kopieren.

D2 : =WENN(ZÄHLENWENN(Schichtplan!$C$4:blush:G$6;A2)+ZÄHLENWENN(Schichtplan!$C$8:blush:G$13;A2)=0;A2;"")
D3 : =WENN(ZÄHLENWENN(Schichtplan!$C$4:blush:G$6;A3)+ZÄHLENWENN(Schichtplan!$C$8:blush:G$13;A3)=0;A3;"")
D4 : =WENN(ZÄHLENWENN(Schichtplan!$C$4:blush:G$6;A4)+ZÄHLENWENN(Schichtplan!$C$8:blush:G$13;A4)=0;A4;"")
usw. in D nach unten kopieren.

Festgelegte Namen:
KW : =Hilf!$E$1:blush:E$53, unbenutzt in Selektion.
Verf : =BEREICH.VERSCHIEBEN(Hilf!$B$2;;;ZÄHLENWENN(Hilf!$C:blush:C;"\>0");1), unbenutzt in Selektion.

A1:E10
haben das Zahlenformat: Standard

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

[Eisb_r13]

Hallo Reinhard,

also erstmal recht herzlichen Dank für Deine Mühe, genau so hab ich mir das vorgestellt. Danke
Excel ist eiin hervorragendes Programm, hätte man doch bloß mal schon eher damit angefangen.
Jetzt muss ich nur mal sehen ob ich das mit dem „Löschbutton“ noch hinbekomme.
Ich werd mal versuchen durch die Formeln durchzusteigen, die sind ja fast selbsterklärend, achso sorry das ich das Blatt an Deine Privatadresse geschickt habe, das nächst Mal lade ich es hoch.

Danke nochmal
MfG Sven

[Eisb_r13]

Hallo nochmal,

vielleicht kannst Du mir die Sache mit den festgelegten Namen mal erklären, insbesondere den mit dem Bereich verschieben.

Danke

[Reinhard_fb8cb7]

vielleicht kannst Du mir die Sache mit den festgelegten Namen
mal erklären, insbesondere den mit dem Bereich verschieben.

Hallo Sven,

bei Daten-Gültigkeit darf man sich mit Formeln die man dort eingeben darf nur auf das Blatt beziehen wo die Daten-Gültigkeit ist.
Aber man darf dort Namen eingeben und diese namen dürfen sich auf andere Blätter beziehen.

D.H. bei Daten–Gültigkeit–Liste darf ich im hauptblatt nicht eingeben:
=Hilf!B2:B22
aber ich darf eingeben
=XYZ
und XYZ ist der Name für
=Hilf!B2:B22

Damit wäre die Aufgabe auch gelöst. Nur ist ja der Bereich Hilf!B2:B22 immer fest. D.H. wenn da leere Zellen drin sind weerden sie in der Auswahlliste mitangezeigt, was ja blöd ist.

Also macht man die Liste dynamisch, sodaß sie immer nur „gefüllte“ Zellen anzeigt.

Im Hilf!C:C kann ich ja bequem die Anzahl der gefüllten Zellen ermitteln mit:
=ZählenWenn(Hilf!C:C;">0")

Das Ergebnis ist irgeneine Zahl von 0 bis X.

Nun will ich ja in der Auswahlliste diese Anzahl an gefüllten Zellen aus Hilf!B2:Bx angezeigt haben, jetzt kommt Bereich.Verschieben ins Spiel.

Wenn ich nun dem Namen „Verf“ (Verfügbar) diese Formel zuweise:
=Bereich.Verschieben(B2;0;0)
oder ist das Gleiche:
=Bereich.Verschieben(B2;
so entspricht das für Excel dem da:
=Bereich.Verschieben(B2;0;0;1;1)
und das hat als Ergebnis B2, denn der Bereich B2 wurde um 0 Zeilen nach unten verschoben, um 0 Spalten nach rechts, der neue Bereih ist 1 Zeile hoch und 1 Spalte breit.

(weggelassene Parameter zählen in dem Fall als 0 oder 1, je nach Parameter)

So, wenn ich nun aus Hilf!B2:Bx 5 Einträge haben will in der Auswahlliste, so vergebe ich für den Namen „Verf“ die Formel.:
=Bereich.Verschieben(B2;0;0;5;1)
Da die 5 ja dynamisch sein soll, ergibt sich die Formel zu:
=Bereich.Verschieben(B2;0;0;ZählenWenn(Hilf!C:C;">0");1)
bzw.
=Bereich.Verschieben(B2;;;ZählenWenn(Hilf!C:C;">0"))
was identisch ist.

Hier kannst du ausgiebig „Bereich.verschieben“ austesten:

http://www.hostarea.de/server-01/Januar-31e4f0234b.xls

Gruß
Reinhard