Live-Forum - Die aktuellen Beiträge
Datum
Titel
19.04.2024 12:58:59
Daten einfügen aus anderem Sheet
19.04.2024 12:23:24
Wenn-Formel anpassen
19.04.2024 12:09:19
Unique (Application.WorksheetFunction.Unique)
19.04.2024 11:51:37
Nächsten identischen Treffer suchen
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
800to804
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
800to804
800to804
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Feiertagskennzeichnung

Feiertagskennzeichnung
16.09.2006 16:02:43
Dirk
Hallo allen EXCEL-Bastlern,
ich raufe mir gerade die Haare über ein Problem, das mir wohl bald den Spitznamen KOJAK einbringen wird. ;-)
In Spalte A soll der Wochentag bei einem Feiertag rot eingefärbt werden (bed. Format ist hinterlegt).
Dazu habe ich mir in Spalte L eine Hilsspalte eingerichtet mit entspr. SVERWEIS. Hier erscheint allerdings lediglich #NV - was anscheinend mit falschen Wertübergaben aus den vorherigen Berechnungen zusammenhängt...
Außerdem erscheint in L11 eine mir unerklärliche Fehlermeldung (warum Zirkelbezug?).
Zur Verdeutlichung hier mal meine bisherigen geistigen Ergüsse (die aus meiner Sicht relevanten Spalten sind GRÜN):

Die Datei https://www.herber.de/bbs/user/36722.xls wurde aus Datenschutzgründen gelöscht

Kann mir jemand helfen bzw. hat eine bessere Idee? Vielleicht geht mein Ansatz ja auch in die falsche Richtung...
Schon mal vielen Dank für eure Bemühungen!
MfG Dirk N.

17
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Feiertagskennzeichnung
16.09.2006 16:12:06
Josef
Hallo Dirk!
Du baust die Tabelle ziemlich kompliziert auf!
Das mit der kennzeichnung geht z.B. so.
Plang

 ABCDEFGHIJ
1    Einsatzplanung     
2  Monat: Oktober   Jahr:2006
3          
4  Runde 4   Runde 5   
5 DatumAnz. d.
Port.		KITAFahrer /-inBemerkungen
Änderungen		Anz. d.
Port.		KITAFahrer /-inBemerkungen
Änderungen
6So01.22xNeumann 30 Wolter 
7Mo02.21 Neumann 31 Wolter 
8Di03.22 Lindemann 32 Wolter 
9Mi04.23xLindemann 33 Wolter 

Formeln der Tabelle
ZelleFormel
B6=R6
B7=B6+1
B8=B7+1
B9=B8+1

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
B61. / Formel ist =WOCHENTAG(B6;2)>5Abc
B62. / Formel ist =ISTZAHL(VERGLEICH(B6;$Z$11:$Z$38;0))Abc
B71. / Formel ist =WOCHENTAG(B7;2)>5Abc
B72. / Formel ist =ISTZAHL(VERGLEICH(B7;$Z$11:$Z$38;0))Abc
B81. / Formel ist =WOCHENTAG(B8;2)>5Abc
B82. / Formel ist =ISTZAHL(VERGLEICH(B8;$Z$11:$Z$38;0))Abc
B91. / Formel ist =WOCHENTAG(B9;2)>5Abc
B92. / Formel ist =ISTZAHL(VERGLEICH(B9;$Z$11:$Z$38;0))Abc


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Sepp
Anzeige
AW: Feiertagskennzeichnung
16.09.2006 16:55:40
Dirk
Hallo Sepp,
ich versinke im Boden...
Deine Lösung ist ja herrlich einfach, wenn man erstmal darauf gekommen ist...
Da hatte ich mich wohl etwas zu sehr verbissen. VIELEN DANK !!!
Allerdings hätte ich doch noch eine Frage, die allerdings mein "Fern"-Ziel ist:
Die Sortierung der Feiertagsliste (welche noch mit einer Geburtstagsliste ergänzt werden soll) hatte ich vorgesehen, da ich unter der eigentlichen Einsatzplanung ein neues "Blatt" (zum Ausdrucken) einrichten möchte.
Darauf sollen dann die FT u. Geburtstage des jeweiligen Monats aufgelistet werden.
Daher versuchte ich mich auch mit dem SVERWEIS in Spalte L, wobei ich natürlich bei Doppelungen erneut auf Schwierigkeiten gestossen wäre...
Hättest du dazu evtl. auch noch eine Idee?
MfG Dirk N.
P.S.: Übrigens noch beste Grüsse von meinem Friseur, der dann wohl doch noch etwas zu tun haben wird (s.KOJAK). ;-)
Anzeige
AW: Feiertagskennzeichnung
16.09.2006 17:04:42
Josef
Hallo Dirk!
Ersteinmal würde ich die Stammdaten in eine separate Tabelle schreiben.
Die Daten lassen sich dadurch leichter Pflegen und die Eigentliche Tabelle wird nicht vollgestopft.
Außerdem berechnest du in deiner Tabelle manche Sachen doppelt (zb. die Datumsberechnungen). Das bläht die Datei auf, macht sie langsam und unsicher.
Wenn man Daten und Auswertung trennt, bleibt die Datei viel flexibler und übersichtlicher.
Zum Auflisten der Feier- und Geburtstage mach ich dir später ein Beispiel.
Jetzt habe ich leider keine Zeit mehr.
Gruß Sepp

Anzeige
AW: Feiertagskennzeichnung
16.09.2006 17:47:15
Dirk
Hallo Sepp,
natürlich hast du Recht mit der Trennung von Stammdaten u. Auswertung.
Bisher scheute ich mich vor solchen "Umbauten" aus Angst, etwas Funktionierendes zu zerstören (Formelbezüge, Verweise, Hyperlinks etc.). Im Laufe der Zeit ist dann alles immer mehr gewachsen, wurde unübersichtlicher u. schlecht erweiterbar.
Doch ich werde mich wohl od. übel doch mal ransetzen und deinen netten Tipp umsetzen.
Außerdem gelobe ich, mir bei zukünftigen Projekten mehr Gedanken beim Grundaufbau zu machen. ;-)
Dann klappt's auch mit der Nachbarin... ;-) (Ach, neeee, das war wohl doch was anderes!)
Ich wünsche dir noch ein schönes WE, freue mich auf dein Beispiel u. danke dir schonmal vorab für deine Hilfe.
MfG Dirk N.
Anzeige
AW: Feiertagskennzeichnung
16.09.2006 23:02:10
Josef
Hallo Dirk!
Hier das Beispiel.
Mehr kann ich nicht machen, weil mir die näheren Informationen fehlen.
Das kannst du aber bestimmt selber ausbauen, sonst weist du ja, wo du Fragen musst;-))
https://www.herber.de/bbs/user/36731.xls
Gruß Sepp
AW: Feiertagskennzeichnung
17.09.2006 00:08:56
Dirk
Hallo Sepp,
ich danke dir vielmals - werde mich dranmachen u. sämtliche Tipps von dir umsetzen.
Da du dir ja echt viel Mühe gegeben hast, möchte ich meinen Dank auch mit einer aufwändigeren Antwort ausdrücken.
Sepp ist zwar (laut Kreuzworträtsel :-)) die Kurzform von Josef, doch in deinem Fall vermute ich eher:
Special Excel Programmer Professional
Solver for Excel Programming Problems
super exzellent prima professionell
Sollte es allerdings bedeuten:
Species Extraterrestrial as a Problem helPer
und in deinen Adern fließen EXCEL-Bits und -Bytes, dann bin ich mir sicher, daß sämtliche User dieses Forum dich gern vor "Mulder & Scully" beschützen werden... Deine Tarnung darf keinesfalls auffliegen! ;-)
Schließlich gibt es viele DAU's, denen schon das Herz hüpft, wenn dein Name in der Antwortliste steht. Das ist schon fast gleichzusetzen mit: Problem gelöst.
Hier tummeln sich echt viele Spezialisten, aber du gehörst sicherlich zu den TOP TEN !!!!
Doch nun genug Balsam für die Seele. ;-)
Ich wünsche dir noch ein wunderschönes WE.
Vielen Dank!
MfG Dirk N.
Anzeige
AW: Feiertagskennzeichnung
17.09.2006 00:17:27
Josef
Hallo Dirk!
Zuerst danke für die Blumen, aber ich bin kein Profi.
Excel und VBA sind eines meiner liebsten Hobbys, nicht mehr und nicht weniger.
Es macht mir einfach Freude, wenn ich anderen helfen kann. Und Außerdem kann
ich dabei immer wieder selber dazulernen.
In meiner Datei sind noch ein paar Fehler bei der bedingten Formatierung.
Wenn du damit Probleme hast, dann helfe ich dir gerne weiter.
Auch ich wünsche dir ein schöne WE.
Gruß Sepp

AW: Feiertagskennzeichnung
17.09.2006 00:52:02
Dirk
Hallo Sepp,
angesichts deiner zahlreichen, fachmännischen u. blitzschnellen Antworten liegt eigentlich die Vermutung nahe, daß du dich professionell mit EXCEL beschäftigst.
Vor ca. einem halben Jahr kam ich beim Googlen zu Herber' Forum u. bin seitdem total begeistert. Zwar habe ich beruflich wenig mit EXCEL zu tun, habe mich eher stiefmütterlich damit befasst, doch beim Stöbern im Forum u. im Archiv entdecke ich immer wieder Anregungen, die mir einige Arbeit abnehmen können.
Und so ist EXCEL auch zu einem meiner Hobbies geworden. Allerdings habe ich zu wenig Freizeit, um mich derart intensiv in diese Problematik zu vertiefen.
Ich bin aber auf der Suche nach einem VBA-Grundlehrgang (möglichst an einem od. mehreren WE). Habe allerdings noch nichts Passendes gefunden (VHS bietet es mangels Nachfrage nicht an; eine andere Bildungseinrichtung nur in den Vormittagsstunden für je 2 Std.) ...
Naja, es wird sich schon noch etwas ergeben. Ich werde jetzt noch etwas basteln bis das Tel. klingelt u. ich meinen Sohn von einer Party abholen darf... ;-))
Bei Fragen werde ich dann einfach fragen - aber sicherlich in einem neuen Thread u. nicht dich persönlich. Du bist ja leider nicht mein Privatdozent!
MfG Dirk N.
Anzeige
AW: Feiertagskennzeichnung
17.09.2006 07:31:34
Dirk
Hallo Sepp,
leider muß ich dich doch nochmal direkt um Hilfe bitten - du kannst dich als "geistiger Urvater" sicherlich am Besten reindenken...
Beim Übertragen auf meine Originaldatei ist mir erst so richtig bewußt geworden, was du alles berücksichtigt hast - abgesehen vom Fachlichen hast du auch jede Menge Fleissarbeit investiert u. an nahezu alles gedacht.
Also du kannst nicht leugnen, daß wenigstens eine meiner 4 "Vermutungen" richtig war... ;-)
Du hattest natürlich auch recht, was die bedingte Formatierung (in 'Plang' B6:B36) betrifft - bin mal wieder an meine Grenzen gestossen u. mein Friseur meldet wieder ernsthafte Bedenken an. ;-)
1. Das Wichtigste dabei ist eigentlich der Abgleich mit dem Bereich: FT_wahr, d.h. hier sollen NUR die Feiertage mit "x" berücksichtigt werden. Also sinngemäß: =WENN(MONAT(FTkz)=MONAT($E$2)*(FT_wahr="x");"okay so";"nix")
Alle meine Versuche schlugen leider fehl u. mit WENN kommt man ja bei der bedingten Formatierung leider nicht weiter (was auch logisch ist).
2. Auch die EXCEL-Grenze von max. 3 bed. Formaten ist schade. So sind ja die Zellen in Spalte B auch dann grau unterlegt, wenn in A gar kein Wert steht. Stört zwar nur geringfügig, aber hättest du dafür evtl. auch eine Lösung? Betroffen ist lediglich B34:B36 - bitte diese Anmerkung nicht belächeln. ;-)
Um nicht gegen die (N)Etikette des Forums zu verstossen, liegt die Priorität natürlich lediglich bei 1.
Übrigens werde ich noch folgendes ändern in 'Plang':
In Spalte O werde ich mir noch die "x" ziehen (in M u. N sollen alle FT u. Events erscheinen) - ansonsten geraten evtl. solche wichtige Highlights wie Valentinstag, Muttertag etc. in Vergessenheit - was dann wieder zu Stress führt. Ansonsten schlummern diese Daten an einer Stelle, die nur selten betrachtet wird...
Und in Spalte R werde ich noch das Alter einfügen - als kl. Spielerei u. eigene "Horizonterweiterung"...
Dank deiner Vorarbeit u. dem Archiv sollte mir dies allerdings allein gelingen!
MfG Dirk N.
Anzeige
AW: Feiertagskennzeichnung
17.09.2006 09:51:16
Josef
Hallo Dirk!
Ich hab' mal ein bischen gebastelt;-))
https://www.herber.de/bbs/user/36732.xls
Das mit den 3 Bedingungen für die Formatierung muss man hinnehmen, oder auf VBA ausweichen, oder auf XL2007 warten.
Wenn du eine Kennzeichnung per VBA möchtest, dann kann ich dir das einbauen.
Beschreibe dann genau, welche Ereignisse wie und wo formatiert werden sollen.
Gruß Sepp
AW: Feiertagskennzeichnung
17.09.2006 19:25:36
Dirk
Hi Sepp,
jetzt muß ich mal den Zeigefinger heben: Da hast du mit "Sachen gespielt", mit denen du nicht spielen "durftest". Ich hatte auch schon angefangen zu basteln u. es funzte auch prima. Doch nun kommst du mit einer eleganteren Lösung daher u. zeigst mir mal wieder meinen Nachholebedarf auf. :-(
Natürlich muss ich mich nun ransetzen u. deine Variante umsetzen. Jetzt bin ich aber bächtig möse - oder so ähnlich... ;-)
Zu meiner letzten mail:
Frage 2 (u. das Kursive) hast du komplett gelöst.
Die Frage 1 habe ich anscheinend nicht eindeutig genug formuliert.
In der Spalte B sollen NUR die Feiertage farblich markiert werden, die auch in LSA gesetzl. FT sind (wo also in 'Stamm' in der Spalte E ein "x" steht).
Geht das nur über VBA - ist das ein großer Aufwand?
Wenn du keine andere Möglichkeit siehst u. du Lust dazu hast, würde ich mich natürlich freuen, wenn du das basteln könntest.
Allerdings ist das aus meiner Sicht schon ausverschämt, denn verstehen od. nachvollziehen kann ich es bestimmt nicht. Das ist ja wie Abschreiben in der Schule vom Nachbarn - zwar hat man momentan die gute Note, aber wenn's drauf ankommt fehlt etwas...
Doch da fällt mir gerade noch etwas Wichtiges ein:
Unter der Tabelle der Einsatzplanung hätte ich gern eine "KontrollZELLE" mit folgendem Ergebnis: WOCHENTAGE des angewählten Monats ABZÜGLICH der Anz. der FT in LSA, die NICHT auf ein WE fallen.
Hintergrund: Vermeidung von falschen Eingaben, denn die KITA's werden nur wochentags beliefert u. diese sind an FT geschlossen.
Doch wie gesagt, oberste Priorität hat eigentlich die bed. Formatierung der Spalte B !
Vielen Dank für alle bisherigen u. künftigen Bemühungen!
MfG Dirk N.
Anzeige
AW: Feiertagskennzeichnung
17.09.2006 22:30:22
Dirk
Hallo Sepp,
nun bin ich voll von den Socken - was hast du denn da wieder gezaubert.
Das ist ja der absolute Hammer... SPITZE !!!
Ich wollte dir gerade meine aktuellste Version uploaden und wiedermal haben wir uns überschnitten.
Was du da alles eingebaut hast ist fantastisch (s. Formatübernahme bei den MA u.u.u.u.).
Allerdings gefällt mir folgendes nicht so recht:
1. Spalte B sollte eigentlich als Hintergrund WEISS sein (die gelben Felder sollen dem User zeigen, wo er Eingaben zu tätigen hat).
2. Besser fand ich auch die Markierung der Geburtstage in der Spalte A (s. deine letzte Version: Tag dann FETT u. ROT).
Geburtstag = Hindergrund blau, Schrift gelb "überlastet" die Spalte einfach zu sehr. Und gerade habe ich mal einen Geb. UND einen FT auf den 18.09. gelegt: Zwar färben sich C u. D dann rot, aber B bleibt blau - was ich persönl. als irritierend empfinde.
Entschuldige bitte meine "Meckerei", aber da es bei dir momentan noch taufrisch in Erinnerung ist, kannst du evtl. diese "Schönheitsfehler" (aus meiner Sicht) mit geringem Aufwand beheben.
Alles andere funzt soweit super u. mit dieser letzten OP wäre alles perfekt.
MfG Dirk N.
Anzeige
AW: Feiertagskennzeichnung
17.09.2006 22:33:20
Dirk
Hallo Sepp,
nun bin ich voll von den Socken - was hast du denn da wieder gezaubert.
Das ist ja der absolute Hammer... SPITZE !!!
Ich wollte dir gerade meine aktuellste Version uploaden und wiedermal haben wir uns überschnitten.
Was du da alles eingebaut hast ist fantastisch (s. Formatübernahme bei den MA u.u.u.u.).
Allerdings gefällt mir folgendes nicht so recht:
1. Spalte B sollte eigentlich als Hintergrund WEISS sein (die gelben Felder sollen dem User zeigen, wo er Eingaben zu tätigen hat).
2. Besser fand ich auch die Markierung der Geburtstage in der Spalte A (s. deine letzte Version: Tag dann FETT u. ROT).
Geburtstag = Hindergrund blau, Schrift gelb "überlastet" die Spalte einfach zu sehr. Und gerade habe ich mal einen Geb. UND einen FT auf den 18.09. gelegt: Zwar färben sich C u. D dann rot, aber B bleibt blau - was ich persönl. als irritierend empfinde.
Entschuldige bitte meine "Meckerei", aber da es bei dir momentan noch taufrisch in Erinnerung ist, kannst du evtl. diese "Schönheitsfehler" (aus meiner Sicht) mit geringem Aufwand beheben.
Alles andere funzt soweit super u. mit dieser letzten OP wäre alles perfekt.
MfG Dirk N.
AW: Feiertagskennzeichnung
17.09.2006 22:49:00
Josef
Hallo Dirk!
Kein Problem, ersetze einfach die "Worksheet_Calculate" Prozedur durch folgenden Code.
(Rechtsklick auf das Blattregister &gt Code anzeigen)
Private Sub Worksheet_Calculate()
Dim rng As Range

On Error Resume Next
GetMoreSpeed

For Each rng In Range("K6:K36") '=Hilfsspalte
  
  If rng = 0 Then
    Range(Cells(rng.Row, 1), Cells(rng.Row, 4)).Font.Bold = False 'Schrift FETT zurückstellen
    Range(Cells(rng.Row, 1), Cells(rng.Row, 4)).Font.ColorIndex = xlAutomatic 'Schriftfarbe zurückstellen
    Range(Cells(rng.Row, 1), Cells(rng.Row, 4)).Interior.ColorIndex = xlNone 'Hintergrund weis
  Else
    'Wochenende
    Cells(rng.Row, 2).Font.Bold = rng > 9999
    Cells(rng.Row, 2).Interior.ColorIndex = IIf(rng > 9999, 15, xlNone)
    'Feiertage
    Cells(rng.Row, 2).Font.ColorIndex = IIf(Cint(Right(rng, 4)) > 999, 3, xlAutomatic)
    'Geburstage
    Cells(rng.Row, 1).Font.ColorIndex = IIf(Cint(Right(rng, 3)) > 99, 3, xlAutomatic)
    Cells(rng.Row, 1).Font.Bold = Cint(Right(rng, 3)) > 99
    'KITA Spalte D
    Range(Cells(rng.Row, 3), Cells(rng.Row, 4)).Interior.ColorIndex = IIf(Cint(Right(rng, 2)) > 9 And (rng > 9999 Or Cint(Right(rng, 4)) > 999), 3, 36)
    
  End If
  
  If rng.Offset(0, 1) = 0 Then '2. Hilfsspalte
    Range(Cells(rng.Row, 7), Cells(rng.Row, 8)).Interior.ColorIndex = xlNone
  Else
    'KITA Spalte H
    Range(Cells(rng.Row, 7), Cells(rng.Row, 8)).Interior.ColorIndex = IIf(Cint(Right(rng.Offset(0, 1), 2)) > 9 And (rng.Offset(0, 1) > 9999 Or Cint(Right(rng, 4)) > 999), 3, 36)
  End If
  
Next

GetMoreSpeed 0
On Error GoTo 0
End Sub


Gruß Sepp

AW: Feiertagskennzeichnung
17.09.2006 23:32:23
Dirk
Hallo Sepp,
welche Zahl muß ich in dem letzten Code ändern, um das richtige (grelle) Gelb zu erhalten?
MfG Dirk N.
AW: Feiertagskennzeichnung
17.09.2006 23:56:17
Dirk
Hi Sepp,
hab'gerade gefunden (36 zu 3).
Ich danke dir tausendmal u. noch einmal mehr... ;-)
Wenn ich mich recht erinnere, hatten wir schonmal das Thema: Golf geordert u. Mercedes geliefert. Auch diesmal war deine Unterstützung mehr als super !!!
Wünsche dir einen stressfreien Wochenbeginn.
MfG Dirk N.

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige