Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1480to1484
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
Inhaltsverzeichnis

Pulldown aus Bereich außer Bestimmte Inhalte

Pulldown aus Bereich außer Bestimmte Inhalte
22.03.2016 14:47:48
Marc
Hallo zusmamen,
folgendes:
Ich habe in einer Tabelle eine Spalte, in dem die Namen der Mitarbeiter stehen.
In der Haupttabelle dann eine Zelle den Namen (per Pulldown auf dem ersten Bereich) der Person, die an diesem Tag Urlaub hat.
In anderen Zellen der gleichen Reihe soll dann ein Pulldown erscheinen, in dem alle Mitarbeiter außer diese eine Person auszuwähmen ist.
Gibt es dazu eine Möglichkeit direkt als Formel?
Ich möchte nicht unbedingt für diese eine Sache per VBA arbeiten.
Vielen Dank für eure Hilfe.

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Deine Frage kann man bejahen, wenn ...
22.03.2016 14:55:57
...
Hallo Marc,
... es immer der gleiche Bezug für die Dropdownzelle(n) ist. Ist das der Fall?
Stelle doch mal eine kleine Dummydatei hier ein, damit es eindeutiger ist.
Gruß Werner
.. , - ...

AW: mit Hilfe von INDEX() und AGGREGAT() ...
23.03.2016 09:36:57
INDEX()
Hallo Marc,
... aber in Deinem Fall dann auch mit Hilfsspalten. Dies würden bei einer reinen Formellösung notwendig, weil der Bezug für die maßgebliche Dropdownzellendefinition sich eben doch ändert. Insofern war meine Vermutung zwar richtig, aber anders als Du es interpretiert hast.
Für nachfolgende formelbasierenden Lösung wäre zwar eine benannte Formel (und zwar : MA_frei für die Dropdownliste) ausreichend, habe aber der Übersichtlichkeit halber zwei weitere definiert.
Im Bereich C2:F## (der Spaltenbereich kann natürlich auch darüber hinaus definiert werden) hat jetzt nur noch die eine Dropdownliste zugewiesen bekommen. Damit bist Du in der Auswahl auch flexibler Du kannst musst aber nicht in Spalte C zuerst wählen)
Im notwendige Hilfsspaltenbereich brauchst Du auch nur noch eine (benannte) Formel. Für die Endanwendnung bietet sich jedoch der zusätzliche Einsatz einer bedingten Formatierung an, der eine Fehleinsatz (Kopieren von Eingabezellen) visuell kenntlich macht (siehe D5:F5)
Natürlich geht es mit VBA ohne den Hilfsspaltenbereich (der natürlich in einer beliebigen freien Spalte beginnen und auch ausgeblendet werden kann) aber es geht eben auch ohne.
Formel M2 nach rechts und unten ziehend kopieren. Dropdownzelle C2 (mit bedingter Formatierung nach rechts und unten)
Fahrplan

 ABCDEFGHIJKLMNOP
1KWMontagFahrerHofLager…          
2107. Mrz.Anton F.         Hans E.Maria G.  
31114. Mrz. Hans E.        Anton F.Maria G.  
41221. Mrz.Maria G.Hans E.Anton F.           
51328. Mrz.Hans E.Anton F. Anton F.      Maria G.   
6144. Apr.          Hans E.Anton F.Maria G. 
71511. Apr. Maria G.        Hans E.Anton F.  

Formeln der Tabelle
ZelleFormel
M2=freie_MAer
Namen in Formeln
ZelleNameBezieht sich auf
M2freie_MAer=WENNFEHLER(INDEX(Mitarbeiter;AGGREGAT(15;6;(ZEILE(Mitarbeiter)-1)/ISTNV(VERGLEICH(Mitarbeiter;Fahrplan!$C2:L2;));1));"")
M2Mitarbeiter=Mitarbeiter!$A$2:INDEX(Mitarbeiter!$A:$A;ANZAHL2(Mitarbeiter!$A:$A))
Namen verstehen

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C2Liste =MA_frei 
Namen in Formeln
ZelleNameBezieht sich auf
C2MA_frei=Fahrplan!$M2:INDEX(Fahrplan!$M2:$ZZ2;SUMME(N(LÄNGE(Fahrplan!$M2:$ZZ2)>0)))
Namen verstehen

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C21. / Formel ist =ZÄHLENWENN($C2:$F2;C2)>1Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
AW: irgendwas klappt da nicht so richtig
23.03.2016 17:45:25
Marc
Hallo Werner,
vielen Dank für die viele Mühe die du dir mit der Beantwortung gemacht hast.
ich habe jetzt für M2 den Namen Mitarbeiter mit freie_MAer kombiniert und das Ganze dann in die Zelle geschrieben.
Allerdings ;Fahrplan!$C2:L2;));1));"") durch ;Fahrplan!$C2:$L2;));1));"") ersetzt.
In der Hilfstabelle erhalte ich nur überall den ersten Namen aus dem Bereich Mitarbeiter!$A:$A
Irgendwo ist ein Haken (ich kenne die Funktion AGGREGAT überhaupt nicht, muss mich da mal reinkniehen).
Dann noch folgendes:
wegen der Übersichtlichkeit habe ich die Hilfstabelle in das Blatt "Mitarbeiter verschoben.
Da ich mit Maximal 8 verschiedenen Namen arbeite ist jetzt dort der Bereich C2:J40 belegt (8 Spalten = Anzahl Möglichkeiten, und 40 Zeilen = Anzahl benötigter Wochen).
Dadurch kann natürlich in der Dropdown die Formel für MA_frei nicht mehr eingegeben werden.
Sollte es jetzt absolut keine andere Möglichkeit geben, muss ich schauen, dass ich die Hilfstabelle in Blatt Fahrplan ziemlich weit nach rechts lege und die Zeilen ausblende.
Ich füge nochmal die angepasste Datei bei: https://www.herber.de/bbs/user/104577.xlsx

Anzeige
AW: nur wenn man nicht richtig abändert ;-) ...
23.03.2016 18:37:03
...
Hallo Marc,
... ich bin Deinen Wunsch nachgekommen und hab die Hilfszellen in die Liste Mitarbeiter verlegt und arbeite jetzt auch nur noch mit einem benannten Namen. Wichtig:den benannten Namen bei einer aktivierter Zelle der Zeile 2 definieren!
Die Verlegung der Hilfszellen erfordert aber der Einfachheit halber zusätzlich Hilfszellen, wo die getroffenen Auswahl in Fahrplan!C2:F## in einem 1:1 Abbild ebenfalls nach Mitarbeiter übergeben wird.
Formel Mitarbeiter!C2 nach rechts bis L2 und nach unten kopieren, Die Formel M2 8 Spalten nach rechts und unten. Und die Dropdownzelle Fahrplan!C2 (mit der bedingten Formatierung!) ebenfalls nach rechts und unten:
Fahrplan

 CDEF
1FahrerHofLager…
2Anton F.   
3  Maria G. 
4 Anton F. Anton F.
5 Maria G.Hans E. 
6    

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C2Liste =MA_frei 
Namen in Formeln
ZelleNameBezieht sich auf
C2MA_frei=Mitarbeiter!$M2:INDEX(Mitarbeiter!$M2:$ZZ2;SUMME(N(LÄNGE(Mitarbeiter!$M2:$ZZ2)>0)))
Namen verstehen

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C21. / Formel ist =ZÄHLENWENN($C2:$F2;C2)>1Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Mitarbeiter

 ABCDEFGHIJKLMNOPQRST
1Mitarbeiter Hilfstabellen
2Hans E. Anton F.         Hans E.Maria G.      
3Anton F.   Maria G.       Hans E.Anton F.      
4Maria G.  Anton F. Anton F.      Hans E.Maria G.      
5   Maria G.Hans E.       Anton F.       
6            Hans E.Anton F.Maria G.     

Formeln der Tabelle
ZelleFormel
C2=INDEX(Fahrplan!C:C;)&""
M2=WENNFEHLER(INDEX($A$2:INDEX($A:$A;ANZAHL2($A:$A)); AGGREGAT(15;6;(ZEILE($A$2:INDEX($A:$A;ANZAHL2($A:$A)))-1)/ISTNV(VERGLEICH($A$2:INDEX($A:$A;ANZAHL2($A:$A)); $C2:L2;)); 1)); "")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
Ja, wenn man es besser wissen will ;-) ...
24.03.2016 11:40:10
Marc
Hallo Werner,
... dann klappt es auch nicht!
Danke für die guten Erklärungen. Die Formeln funktionieren perfekt.
Ich habe allerdings die Seite Mitarbeiter an die wirklich nötige Anzahl Spalten angepasst.
Trotzdem, um meinen Wissensdurst zu stillen:
Wie würde denn eine VBA-Funktion aussehen, basierend auf die Namen in Spalte Mitarbeiter!$A:$A?
Momentan tue ich mich damit schwer.
Nochmals vielen Dank.

AW: nun als VBA-Lösung gesucht ...
24.03.2016 12:02:36
...
Hallo Marc,
... dazu kann ich nur schreiben, dass ich mich aus verschieden Gründen aus VBA-Lösungen heraus halte.
Aber hier im Forum gibt es genügend gut VBAler, die Dir da sicherlich helfen können.
Ich stell den thread deshalb schon auf offen (erkenntlich an den roten Ausrufezeichen).
Hab allerdings etwas Geduld, wenn jemand von den Erwähnten Zeit und Lust hat, wird er sich dessen auch annehmen.
Gruß Werner
.. , - ...

Anzeige
AW: VBA-Lösung, läuft aber nicht perfekt
24.03.2016 21:48:47
Marc
Hallo Klaus,
vielen Dank für die schnelle Antwort und die schöne Lösung.
Allerdings funktioniert sie nicht immer korrekt.
Wenn man z.B. einen Namen in Zelle C2 eingegeben hat, dann die folgenden Zellen D2.. ausfüllt klappt das. Sobald aber die Zelle C2 gelöscht wird, bleiben die Pulldowns daneben aktiv (nur nach einmaligem Anspringen einer Zelle nachdem Cx beschrieben wurde, sonst gibt es kein Pulldown).
Es ist aber auch möglich in Zelle C2 den selben Namen wie in einer der folgenden Spalten auszuwählen nachdem alle Spalten beschrieben sind, was dann zu doppelten Einträgen in einer Reihe führen würde.
Das kann zwar mit bedingten Formatierungen angezeigtt werden, wäre aber, wenn man schon VBA nutzt schade ;-)
Da wäre es dann in meinen Augen besser, immer jede Zeile zu durchlaufen, um überhaupt doppelte zu unterbinden.
Das wird sicherlich aufwendiger sein und auch langsamer laufen.
Wäre das denn möglich?

Anzeige
VBA-Lösung nach Deiner Vorgabe
24.03.2016 23:05:39
KlausF
Hallo Marc,
bei Excel-Profi und VBA gut sollte man erwarten, dass die Aufgabenstellung präzise formuliert ist.
Von Löschen der Zellen in Spalte C nebst dazu gehörenden Aktionen stand nichts in der Anforderung.
[ Es ist aber auch möglich in Zelle C2 den selben Namen wie in einer der folgenden Spalten auszuwählen nachdem alle Spalten
beschrieben sind, was dann zu doppelten Einträgen in einer Reihe führen würde. ]
Du meinst, wenn alle Spalten beschrieben sind willst Du nachträglich in C ändern und dann keine doppelten vorfinden?
Das würde bedeuten, dass die Zellen in Spalte C ein abnehmendes DropDown-Feld zu den Spalten D:F haben müssen.
Sorry, aber auch davon stand nichts in der Aufgabenstellung und ist mV auch ein ganz anderer Ansatz und
mit erheblich mehr Aufwand verbunden.
Beantworte bitte zwei Fragen.
1. Was genau soll geschehen, wenn ein Name in Spalte C gelöscht wird?
2. Darf es sein, dass in D:F Namen doppelt (dreifach) auftreten?
Ob ich dann noch Lust und Zeit dafür finde weis ich nicht.
Gruß
Klaus

Anzeige
AW: VBA-Lösung nach Deiner Vorgabe
25.03.2016 01:21:15
Marc
Hallo Klaus,
danke für deine Antwort und Nachfrage zum Thema. Ich wollte dich jetzt nicht brüskieren.
Im Anfang des Theads ging es um eine reine Excel-Lösung ohne VBA. Das ist auch sehr gut geworden.
Die VBA-Lösung ist nur für mich als Erweiterung meines Wissens gedacht, nicht zur tatsächlichen Verwirklichung.
Die Arbeit wird auf vielen verschiedenen PCs ausgeführt werden, was dann meist dazu führt, dass die Programme erst einmal blockiert werden, was sich via VBA dann recht schwierig gestaltet weil nicht jeder Benutzer das kennt.
Mit VBA wäre natürlich eine Lösung, bei der alle Zellen einer Reihe abgedeckt sind deren Werte sich gegenseitig ausschließen, also jeder Name nur ein Mal vorkommen darf, eine tolle Sache.
Ich danke da erst einmal daran, die Liste der Mitglieder in einem globalen Array zu speichern um die Namen nur einmal auslesen zu müssen (außer wenn sich in der Liste selbst etwas ändert). Das dürfte die Geschwindigkeit der Erstellung der Pulldowns erhöhen. Weiter sind meine Ideen aber noch nicht gereift.
Vielleicht findest du ja doch Zeit und Lust hier ein wenig weiter zu spinnen ;-)

Anzeige
VBA-Lösung neu
25.03.2016 17:57:16
KlausF
Hallo Marc,
so sollte es nun funktionieren. Lösung erfolgt über drei Namen, davon einer temporär.
Die Vorlage ist allgemein gefasst und kann von den Bereichen leicht angepasst werden.
https://www.herber.de/bbs/user/104608.xls
Eine schöne Osterzeit
Gruß
Klaus

AW: VBA-Lösung neu
29.03.2016 18:41:53
Marc
Hallo Klaus,
danke für die Osterwünsche. Jetzt ist es leider zu spät, sie zu erwidern und vielen Dank für deine Anstrengungen. Ich habe einiges dazugelernt.
Die VBA funktioniert prächtig.
Ich habe noch ein wenig daran herumgefeilt.
Es gab eine Fehlermeldung wenn man auf ein Feld klickt und es keinen oder nur einen Namen bei den Mitarbeitern gab. Das ist zwar schwer nachvollziehbar, könnte aber bei einem User, der die Datei leer erhält direkt einen schlechten Eindruck hinterlassen.
Anbei diese kleine Korrektur (für die Nachwelt, falls ein anderer Benutzer den Kode braucht).
https://www.herber.de/bbs/user/104654.xlsm
Danke nochmals.
Was ich bei Gelegenheit und wenn ich mal Freizeit habe noch weiter zu verbessern versuche ist, eine Möglichkeit zu finden, dass das Programm auch noch die zwei "manuellen" Eingaben, also den Bereichsnamen "rngPull" und die den VBA-Zeile "Set rngTMP = Range("C" & Target.Row & ":F" & Target.Row)" im WS_SelectionChange von "Fahrplan" automatisch berechnen zu lassen.
Anzeige

299 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige