Live-Forum - Die aktuellen Beiträge
Datum
Titel
09.05.2024 15:49:16
OEE Verlust als Linie an OEE
09.05.2024 13:10:18
Pivot-Tabelle mit Monaten und nicht Tagen
09.05.2024 11:36:45
workbook schließen ohne abfrage
09.05.2024 08:18:18
Feldformatierung (Einzahl/Mehrzahl) ohne VBA
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1780to1784
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
1780to1784
1780to1784
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Jahreskalender mit bedingter Formatierun

Jahreskalender mit bedingter Formatierun
04.09.2020 13:04:20
reiner
hallo Leute,
https://www.herber.de/bbs/user/140046.xlsx
in dem Kalender werden Ferienzeiten in den Jahren 2013 bis 2025 mit nachfolgender Formel in der bedingten Formatierung angezeigt (Bereich B3:B33), in den anderen Spalten entsprechend angepasst:
Da ich den kompletten Text nicht hochladen konnte, da er Zeichen enthält, die nicht akzeptiert werden, habe ich die komplette Beschreibung auf dem Tabellenblatt als Textfeld eingefügt.
mfG
reiner

38
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Problem bei Inhalten erkannt ...
04.09.2020 13:17:29
Matthias
Hallo,
Userbild
öffen bei mir also nicht ohne weiteres möglich.
Ich kann nicht einfach der Quelle der Arbeitsmappe vertrauen
wenn ich die Mappe nicht kenne.
Lasse deshalb den BT offen
Gruß Matthias
AW: ungünstige Datenstruktur ...
04.09.2020 13:24:59
neopa
Hallo Reiner,
... Deine Daten in Deinem Kalender sind alles Textwerte. Diese müssten in zunächst alle erst wieder in echte Datumswerte gewandelt werden um diese mit den echten Datumswerten Deiner Ferienzeiten zu vergleichen. Diese sind zudem noch mit Start und Ende untereinander in einer Spalte angeordnet, was zusätzlich die Auswertung erschwert.
Gruß Werner
.. , - ...
Anzeige
AW: ungünstige Datenstruktur ...
04.09.2020 14:01:00
reiner
hallo Werner,
ich kann dir nicht folgen, wo ist das Problem?
Die Anzeige der Ferienzeiten funktioniert einwandfrei je nach Jahr.
Das soll auch nicht geändert werden; es geht im Moment nur darum, die Formel anzupassen, sodass die Ferienzeiten nur angezeigt werden, wenn AA3 =1 ist
AW: ungünstige Datenstruktur ...
04.09.2020 14:14:33
SF
Hola,
=$AA$3=1)*(Deine_Formel_in_der_bedFormatierung)

Gruß,
steve1da
AW: ungünstige Datenstruktur ...
04.09.2020 15:11:08
reiner
hallo steve,
das ist dann wohl die Lösung, danke dir
aber gerade sehe ich ein bisher nicht erkanntes Problem: wenn das Kalenderblatt am 01.Juli beginnt, werden im Oktober (Spalte "H") keine Ferien angezeigt;
wenn das Kalenderblatt dagegen am 01.Januar beginnt, werden im April (Spalte "H") sowie im Oktober (Spalte "T" Ferien korrekt angezeigt
wenn du dieses Problem nachvollziehen möchtest, dann ändere bitte die Formel in der bed. Form. im Bereich T3 bis T33 
=(T3"")*SUMME((AK3>=L5_:AL23_)*(REST(ZEILE(L5_:L23_);2)=1)*(AK36)))

ich kann nicht erkennen das die Formel im Bereich "H" von der Formel im Bereich "T" abweicht, vielleicht bin ich etwas "betriebsblind" da ich schon länger an diesem Problem arbeite
Anzeige
hallo steve
04.09.2020 17:02:39
reiner
ich hatte dir bereits geschrieben aber womöglich vergessen das Kontrollkästchen zu aktivieren
hier noch einmal der Text:
hallo steve,
das ist dann wohl die Lösung, danke dir
aber gerade sehe ich ein bisher nicht erkanntes Problem: wenn das Kalenderblatt am 01.Juli beginnt, werden im Oktober (Spalte "H") keine Ferien angezeigt;
wenn das Kalenderblatt dagegen am 01.Januar beginnt, werden im April (Spalte "H") sowie im Oktober (Spalte "T" Ferien korrekt angezeigt
wenn du dieses Problem nachvollziehen möchtest, dann ändere bitte die Formel in der bed. Form. im Bereich T3 bis T33
=(T3"")*SUMME((AK3>=L5_:AL23_)*(REST(ZEILE(L5_:L23_);2)=1)*(AK36)))
ich kann nicht erkennen das die Formel im Bereich "H" von der Formel im Bereich "T" abweicht, vielleicht bin ich etwas "betriebsblind" da ich schon länger an diesem Problem arbeite
Anzeige
AW: Du meinst also nur Deine bed Format. ...
04.09.2020 14:23:24
neopa
Hallo Reiner,
... im Tabellenblatt "Ferienzeiten" und nicht letztere im Jahreskalender anzeigen?
Wenn dem dann so sein sollte, dann lösche alle Deine vielen vorh. bed. Formatierungen
Markiere dann dort N3: AL24 und vergib folgende bed. Formatierungsformel:
=(N$3=$A$1)*(N3"")*Jahreskalender!$AA$3
Gruß Werner
.. , - ...
AW: Du meinst also nur Deine bed Format. ...
04.09.2020 15:13:19
reiner
Werner, irgendwie verstehst du mich nicht richtig
es geht um die Darstellung der Ferienzeiten im Blatt "Jahreskalender", siehe Beispieldatei. das funktioniert auch wunderbar (in Spalte "T" habe ich testweise die Formel geändert)
Steve hat mir einen funktionierenden Änderungsvorschlag unterbreitet
Anzeige
AW: das liegt wohl dann daran ...
04.09.2020 16:09:06
neopa
Hallo Reiner,
... dass in meiner eingespielten Datei im Tabellenblatt "Jahreskalender" keinerlei bedingte Formatierungsformeln vorhanden sind, dies weder in Spalte T noch sonstwo. Möglicherweise weil mein Excel beim einlesen Deiner Datei auf Fehler hingewiesen hat (wie auch bei Matthias und diese eliminiert hat.
Gruß Werner
.. , - ...
AW: das liegt wohl dann daran ...
04.09.2020 16:56:46
reiner
kann sein, bei steve hat es aber scheinbar funktioniert
AW: eine Formel ist ausreichend ...
05.09.2020 18:30:18
neopa
Hallo Reiner,
... und diese würde ich als benannte Formel in B3 definieren zu:
_Ferien:
=SUMME((INDEX(!$AB$1:$AM$33;ZEILE();SPALTE(!B3)/2)>=Ferienzeiten!$L$5:$L$23)*(INDEX(!$AB$1:$AM$33; ZEILE();SPALTE(!B3)/2)"")
und als bedingte Formatierungsformel für =$B$3:$X$33 dann nur noch: =_Ferien.
Gruß Werner
.. , - ...
Anzeige
AW: eine Formel ist ausreichend ...
05.09.2020 18:39:25
reiner
hallo werner,
ich teste deinen Vorschlag, aber erst am sonntag
bis dahin, vorab vielen Dank
AW: eine Formel ist ausreichend ...
06.09.2020 00:17:49
reiner
hallo Werner,
dein Vorschlag klingt interessant, ich weiß aber ehrlich gesagt nicht wie ich ihn realisieren soll, weil in Zelle "B3" bereits eine Formel enthalten ist die ich nicht löschen will weil sie benötigt wird.
Bitte sei so gut und füge die Formel wie von dir beschrieben in meine hochgeladene Beispieldatei ein und sende sie mir zurück
vielen dank dafür im voraus
AW: es ist keine Zellformel, ...
06.09.2020 08:30:01
neopa
Hallo Reiner,
... sondern diese wird so wie angegeben lediglich bei aktivierter Zelle B3 im Namensmanager als (z.B.) _Ferien definiert und dies dann als benannte Formel in der bedingten Formatierung für den angegebenen Bereich eingesetzt.
Mehr zum Namensmanager sieh u.a. auch mal hier: https://www.online-excel.de/excel/singsel.php?f=60 und ff.
Du könntest natürlich anstelle dessen auch die Formel ohne die führenden "!" also so:
=SUMME((INDEX($AB$1:$AM$33;ZEILE();SPALTE(B3)/2)&gt=Ferienzeiten!$L$5:$L$23)*(INDEX($AB$1:$AM$33;ZEILE();SPALTE(B3)/2)&lt=Ferienzeiten!$L$6:$L$24*REST(ZEILE(B$5:B$23);2)))*$AA$3*(B3"")
direkt in B3 als bedingte Formatierungsregelformel für =$B$3:$X$33 einsetzen.
Gruß Werner
.. , - ...
Anzeige
so kommen wir nicht weiter
06.09.2020 11:28:52
reiner
hallo Werner,
Du könntest natürlich anstelle dessen auch die Formel ohne die führenden "!" also so:
=SUMME((INDEX($AB$1:$AM$33;ZEILE();SPALTE(B3)/2)>=Ferienzeiten!$L$5:$L$23)*(INDEX($AB$1:$AM$33; ZEILE();SPALTE(B3)/2)"")
direkt in B3 als bedingte Formatierungsregelformel für =$B$3:$X$33 einsetzen.
ich habe versucht deinen letzten Vorschlag umzusetzen, aber es hat nicht funktioniert, siehe Bild.
Userbild
Werner, ich bitte dich nochmals in die von mir hochgeladene Beispieldatei deine Formel einzufügen, alternativ auch gern mit der in Zelle "B3" definierten Formel
und diese Beispieldatei danach erneut hochzuladen
Dann kann ich mich von der Funktion überzeugen, so kommen wir nicht voran
Anzeige
AW: Deine aufgezeigte Fehlermeldung ...
06.09.2020 12:49:00
neopa
Hallo Reiner,
... macht mich etwas stutzig. Mit welcher XL-Version arbeitest Du?
In meiner XL-2010er Version gibt es keine Probleme.
Hier beigefügt nun Deine Datei mit meiner benannten bed. Formellösung.
https://www.herber.de/bbs/user/140065.xlsx
Gruß Werner
.. , - ...
AW: Deine aufgezeigte Fehlermeldung ...
06.09.2020 13:38:00
reiner
hallo werner,
danke für die Übersendung der Beispieldatei, ich arbeite mit Excel 2007
Die Anzeige der Ferienzeiten bei Kalenderbeginn 01. Januar ist fehlerfrei, jedoch fällt mir auf, dass bei Kalenderbeginn 01.Juli die Anzeige der Ferien am 01.Januar endet!!
Es werden überhaupt keine Ferien im Zeitraum 02.Januar bis 30.Juni angezeigt!! Woran kann das liegen?
Ich habe auch unter Excel 2019 getestet
Mein ursprüngliches Anliegen war, die Anzeige der Ferien soll nur erfolgen wenn Zelle "AA3" eine "1" enthält, bei einer anderen Zahl sollen keine Ferien (sondern Urlaubsreisen, werde ich später nachrüsten) angezeigt werden, kannst du bitte die Formel entsprechzend anpassen?
Anzeige
AW: kann ich so nicht nachvollziehen ...
06.09.2020 15:50:58
neopa
Hallo Reiner,
... bei mir werden die Ferien auch korrekt angezeigt, wenn AA1=7 und wenn Deine Datenwerte in Ferienzeiten!L5:L24 entsprechend der geänderten Kalenderdaten noch anpasst werden. Das hatte ich in Deiner Datei noch nicht getan, weil ich davon ausgegangen bin/ ausgehe, dass Du das noch machst wenn Du wirklich Dein "Jahreskalender" über AA1 "dynamisch" gestalten willst.
Die Berücksichtigung von AA3=1 ist bei meiner Lösung allerdings schon gewährleistet. Das kannst Du doch leicht nachprüfen.
Gruß Werner
.. , - ...
AW: kann ich so nicht nachvollziehen ...
06.09.2020 16:30:27
reiner
hallo werner,
ich lese jetzt erstmals dass ich
Datenwerte in Ferienzeiten!L5:L24 entsprechend der geänderten Kalenderdaten noch anpassen soll
was bitte soll ich anpassen? Ich will es gern tun, muss aber wissen was aus deiner Sicht anzupassen ist.
Ich habe eben noch mal unter Excel 2010 getestet, in den Monaten Janaur bis Juni werden die Ferien nur Anfang Januar angezeigt wenn Kalenderbeginn 01.Juli
Anzeige
AW: hierzu ...
06.09.2020 16:40:48
neopa
Hallo Reiner,
... wenn Deine Jahreskalenderdaten nicht nur die Daten eines Jahres beinhalten sondern die aus 2 Jahren teilweise beinhalten, dann muss Deine Ferienzeitermittlung Ferienzeiten!L5:L24 natürlich auch diese entsprechend ermittelt werden, analog wie das in Jahreskalender AD:AM getan wird.
Ist es so verständlich(er)?
oT. Könnte es sein, dass Du mit einer Vorphase Deiner Datei schon mal hierzu im Forum nachgefragt hast. Mir ist nämlich so, als dass ich diese zumindest schon mal gesehen hätte. Oder?
Gruß Werner
.. , - ...
AW: hierzu ...
06.09.2020 19:47:29
reiner
hallo werner,
ich möchte dem vielleicht entstandenen Eindruck entgegenhalten, dass ich mich nicht auf das Verfassen einer Frage zu einem Excel-Problem beschränke und auf eine möglichst fertige Lösung warte.
Im Rahmen meiner Kenntnisse versuche ich schon selbst ein Problem zu lösen; das es nicht immer klappt, beweist das aktuelle Problem.
Den Gedanken, die Ferienzeiten aufzuteilen hatte ich auch schon nach meinem letzten Beitrag.
Womöglich muss ich die Zeiten, wie du schon angedeutet hast, aufteilen.
Du hast Recht, ich hatte Anfang Juli schon einmal einen Beitrag im Forum zum Thema Kalender. Ich bin mir auch ziemlich sicher, dass du damals einen Beitrag bezgl. der anzuwendenden Formel hochgeladen hattest.
in dieser Datei funktioniert die Darstellung der Ferienzeiten unabhängig vom kalenderbeginn 01. Januar / 01. Juli fehlerfrei.
https://www.herber.de/bbs/user/140075.xlsb
Ich habe den Verdacht, dass durch das Einfügen zusätzlicher Spalten zwischen den einzelnen Monaten das jetzt erkannte Problem entstanden ist.
AW: eingestellte Datei hat unlesbaren Inhalt ...
07.09.2020 08:08:35
neopa
Hallo Reiner,
... und da ich xlsb und xlsm-Dateien sowieso nur ohne vorhandenen VBA-Inhalt einspiele, speicher und lade Deine Datei nochmal als xlsx-Datei hier hoch. Gut wäre es auch, wenn Du den Link auf den Vorgängerthread hier einstellst. Ich schau es mir dann am Nachmittag an.
Gruß Werner
.. , - ...
AW: auch diese Datei ist fehlerbehaftet ...
07.09.2020 12:07:14
neopa
Hallo Reiner,
... ich bastele am Nachmittag später eine Lösung auf Basis der von mir am WE eingestellten Datei.
Der Link auf Deinen alten thread würde mich aber schon noch interessieren.
Gruß Werner
.. , - ...
AW: erfolgt in AA1 nur Auswahl zwischen 1/7 ? owT
07.09.2020 14:38:37
neopa
Gruß Werner
.. , - ...
AW: erfolgt in AA1 nur Auswahl zwischen 1/7 ? owT
07.09.2020 14:56:31
reiner
nein; Auswahl "1/2"
bei Kalenderbeginn 01.Januar enthält "AA1" den Wert "1"
bei Kalenderbeginn 01.Juli enthält "AA1" den Wert "2"
AW: dann folgende Formel ...
07.09.2020 15:10:12
neopa
Hallo Reiner,
... in Ferienzeiten!L5:
=INDEX(N:AL;;VERGLEICH(Jahreskalender!B$1+(Jahreskalender!AA$1=2)*(ZEILE()&lt17);N$3:AL$3;0))
und diese Formel nach unten kopieren.
Gruß Werner
.. , - ...
AW: dann folgende Formel ...
07.09.2020 19:42:24
reiner
hallo Werner,
ich hatte erst jetzt Gelegenheit mir deine Rückmeldung anzuschauen und die Formel in die Beispieldatei 140065.xlsb wie angegeben einzufügen. Damit werden alle Ferienzeiten korrekt angezeigt unabhängig vom voreingestellten Kalenderbeginn.
das ist Super!! vielen Dank für die geduld und deinen Einsatz
Ich will die Formel verstehen und bitte um eine kurze Erklärung:
=INDEX(N:AL;;VERGLEICH(Jahreskalender!B$1+(Jahreskalender!AA$1=2)*(ZEILE()
(ZEILE()wofür steht
danke im voraus
reiner
AW: im dem Fall ist die ZeilenNr. 17 gemeint ...
07.09.2020 20:10:38
neopa
Hallo Reiner,
... in dieser Zeile steht das Startdatum Deiner "Sommerferien", welche bei euch offensichtlich immer im Juli beginnen. Und Juli war ja das Startdatum für Deinen spez. Jahreskalender, wenn in Jahreskalender!AA1 eine 2 steht.
Gruß Werner
.. , - ...
AW: im dem Fall ist die ZeilenNr. 17 gemeint ...
07.09.2020 20:19:14
reiner
hallo werner,
alles klar, nochmals vielen Dank
reiner
AW: bitteschön owT
08.09.2020 10:59:43
neopa
Gruß Werner
.. , - ...
hallo
08.09.2020 16:05:32
neopa C Wener
hallo Werner,
auch wenn ich die Anfrage als erledigt betrachtet habe, wende ich mich nochmals an dich und zwar ist mir aufgefallen, dass in der bisherigen Beispieltabelle 140065.xlsx mit der zuletzt übermittelten Formel die Umschaltung und Zuweisung der Ferienzeiten korrekt erfolgt wenn der Kalender am 01.januar beginnt. Wenn er jedoch am 01.juli beginnt, fehlen in den Jahren 2013, 2016, 2017, 2018 und 2024 die ersten Ferientage der Sommerferien da sie in der letzten Juniwoche beginnen.
Du hattest die Umschaltung 1. / 2 .Halbjahr mit (ZEILE() Ich könnte diesen Fehler umgehen indem ich z.B. in 2013 den Zeitraum 27.06.2013 bis 30.06.2013 in die darüberliegenden Zeilen von Brückentag 3 und in Zeile 17 den Ferienbeginn 1.07.2013 eintrage, das würde auch funktionieren.
https://www.herber.de/bbs/user/140130.xlsx

Ich habe stattdessen versuchsweise 2 weitere Zeilen zwischen Zeile 16 und 17 eingefügt um die "Junitage" der Sommerferien in diese neuen Zeilen einzutragen. (ZEILE() Die zu "_Ferien" gehörende Formel im Namens-Manager wird entsprechend automatisch angepasst, es funktioniert nur nicht. Woran kann das liegen?
Mir ist auch aufgefallen, dass die besagte Formel -wenn ich sie von Hand anpasse- zwar korrekt eingefügt werden kann, aber bei einer Überprüfung stellte ich fest, dass Zellen- und Spaltenbezeichnungen gelegentlich verändert waren ohne mein Zutun, kannst du ein solches Verhalten bestätigen?
reiner
AW: hallo
08.09.2020 16:06:50
neopa C Wener
hallo Werner,
auch wenn ich die Anfrage als erledigt betrachtet habe, wende ich mich nochmals an dich und zwar ist mir aufgefallen, dass in der bisherigen Beispieltabelle 140065.xlsx mit der zuletzt übermittelten Formel die Umschaltung und Zuweisung der Ferienzeiten korrekt erfolgt wenn der Kalender am 01.januar beginnt. Wenn er jedoch am 01.juli beginnt, fehlen in den Jahren 2013, 2016, 2017, 2018 und 2024 die ersten Ferientage der Sommerferien da sie in der letzten Juniwoche beginnen.
Du hattest die Umschaltung 1. / 2 .Halbjahr mit (ZEILE() Ich könnte diesen Fehler umgehen indem ich z.B. in 2013 den Zeitraum 27.06.2013 bis 30.06.2013 in die darüberliegenden Zeilen von Brückentag 3 und in Zeile 17 den Ferienbeginn 1.07.2013 eintrage, das würde auch funktionieren.
https://www.herber.de/bbs/user/140130.xlsx

Ich habe stattdessen versuchsweise 2 weitere Zeilen zwischen Zeile 16 und 17 eingefügt um die "Junitage" der Sommerferien in diese neuen Zeilen einzutragen. (ZEILE() Die zu "_Ferien" gehörende Formel im Namens-Manager wird entsprechend automatisch angepasst, es funktioniert nur nicht. Woran kann das liegen?
Mir ist auch aufgefallen, dass die besagte Formel -wenn ich sie von Hand anpasse- zwar korrekt eingefügt werden kann, aber bei einer Überprüfung stellte ich fest, dass Zellen- und Spaltenbezeichnungen gelegentlich verändert waren ohne mein Zutun, kannst du ein solches Verhalten bestätigen?
reiner
AW: für diese "Spezialfälle" schlage ich vor ...
08.09.2020 16:26:17
neopa
Hallo Reiner,
... dass den jeweiligen Juni-Sommerferien-Tage dieser Jahre in in der Spalte N:AZ in den Zeilen 15 und 16 erfasst. Also z.B. in N15 27.06.2013 und in N16 30.06.2013 und analog für die anderen Jahre.
Das ist jedenfalls so die einfachste Lösung für die bedingte Formatierung In C15 kannst Du ja schreiben:
"Brückentag 3 / Anfangs-Juni-Sommerferientage"
Alles andere wird wieder aufwendiger.
Gruß Werner
.. , - ...
AW: für diese "Spezialfälle" schlage ich vor ...
08.09.2020 16:42:34
reiner
danke werner
AW: für diese "Spezialfälle" schlage ich vor ...
08.09.2020 16:43:56
reiner
danke werner
AW: bitteschön owT
08.09.2020 16:52:53
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige