---

Hallo Excel-Profis,

ich habe versucht mit Bedingter Formatierung meine Terminliste zu bearbeiten,
bekomme aber nicht das gewünschte Ergebniss.
Ich möchte meine Aufträge mit 3 Farben hervorheben.
Wenn der Liefertermin mit der Aktuellen KW übereinstimmt soll die entsprechende Zelle
mit grün ausgefüllt werden.
Wenn der Liefertermin akt.Kw +1 bis +4 ist, soll die Zelle mit gelb ausgefüllt werden.
Wenn der Liefertermin akt.KW +5 +9 ist, dann Zelle grau füllen.

Ist das der Richtige Weg das mit Bedingter Formatierung zu machen?
Wie sollte die Formatierung denn aussehen?
Bedingte Formatierung, Formel für ?? B5 Wenn(F5=A1) bringt keine Farbfüllung

Listenaufbau
In Spalte A1 steht die aktuelle KW [A1="KW "&KALENDERWOCHE(HEUTE())]
In Spalte B stehen meine Aufträge [B5=LA4711]
In der Spalte F stehen Liefertermine [F5=KW 38]

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hallo Marco,

damit Excel rechnen kann, markiere Spalte A - Format - Zellen - Zahlen - Benutzerdefiniert: "KW "0
OK
Damit wird KW 36... angezeigt, es ist aber 'ne echte Zahl. Da packst Du Deine Formel rein: =KALENDERWOCHE(HEUTE())
Mit dem Pinsel überträgst Du das Format in Spalte F. Dort musst Du dann nur Zahlen eingeben - 37, 40, ...

Bedingte Formatierung ist richtig.

Markiere die Zellen A5 bis A... - A5 ist die aktive Zelle (sie ist weiß hinterlegt, alle anderen im markierten Bereich leicht blau). Bedingte Formatierung - (neue Regel) - Formel ist: =A5=F5 [ohne die $!]
Formatieren Muster: Grün
Hinzufügen - neue Regel: =UND(F5>A4+1;F5<=A5+4)
[Edit: Das "+1" ist hier natürlich Quatsch. Das ">" alleine reicht aus. Also: =UND(F5>A4;F5<=A5+4) /Edit]
Formatieren Muster: Gelb
Für +5 und +9 analog.

Bedingte Formatierungen mit Formeln lassen sich einfach testen, indem Du die Bedingung erstmal ganz "klassisch" in eine Zelle eingibst. Dann siehst Du sofort, ob WAHR oder FALSCH.

HTH
Frederik

------------------
DIN1055.de  |  Lastannahmen für Anwender

[Diese Nachricht wurde von Paulchen am 19. Sep. 2008 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hy Frederik,

vielen Dank für Deine Hilfe, klappt genauso wie ich das brauche.
Wie kann ich die Formatierung auf weitere Zellen übertragen ohne viel Arbeit?
Ich habe ca. 600 Zeilen und so muss ich ja in jede Zelle der Spalte um die drei Bedingungen einzutragen.
Ich kann die Bedingung ja nicht für die Spalte A festlegen da diese ja auch Zeilengebunden ist.

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Dazu gibt es Format übertragen,

Ursprungszelle anklicken,
Formatübertragen anklicken (Icon sieht wie ein Malerpinsel aus),Ein Doppelklick klemmt den Befehl.
Zielzellen anklicken.

BTW, Kalenderwoche entspricht nicht der DIN ;-)

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Moin, moin,

das funktioniert bei mir nicht so wie ich es brauche.
Es kann ja sein das der Fehler zwischen Stuhl und Tastatur ist.

Zelle B5 hat die Regeln:
1. Regel: =F5=A1
2. Regel: =UND(F5>A1,F5<=A1+4)
Zelle B6 muss ja dann die Regelen aber mit Bezug auf F6 und nicht F5 haben.

Wenn ich jetzt mit dem Pinsel dies übertrage bekomme ich jedesmal den Bezug auf F5
oder habe ich ein falsche Vorgehensweise?
Muss ich da irgendwo mit $ arbeiten?
Ich habe nachstehendes getestet aber auch ohne Erfolg.
1. Regel: =F5=A$1
2. Regel: =UND(F5>A$1,F5<=A$1+4)

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Zitat:

---

Muss ich da irgendwo mit $ arbeiten?

---

Ja  .

Das $-Zeichen sperrt immer das Zeichen, vor dem es steht.

A1 wird zu A2, B2, ...
$A1 "friert" die Spalte: $A2, $A3 (auch in Spalte B, C, ...)
A$1 "friert" die Zeile: A$1, B$1 (auch in Zeile 2, 3, ...)
$A$1 schließlich bleibt immer auf die Zelle A1 bezogen, egal, wohin Du kopierst.

Ausprobieren! Die Taste F4 schaltet zwischen den absoluten Bezügen durch - siehe auch Excel-Hilfe zu "relativ".

------------------
DIN1055.de  |  Lastannahmen für Anwender

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Zitat:

---

Wie kann ich die Formatierung auf weitere Zellen übertragen ohne viel Arbeit?

---

Eben erst so recht verstanden... siehe mein erster Beitrag: Markiere Deine 600 Zeilen und formatiere für die aktive Zelle.

Zitat:

---

Markiere die Zellen A5 bis A... - A5 ist die aktive Zelle (sie ist weiß hinterlegt, alle anderen im markierten Bereich leicht blau). Bedingte Formatierung - (neue Regel) - Formel ist: =A5=F5 [ohne die $!]

---

Das sollte die Formatierung auf die Zellen A6... A600 übertragen?!

------------------
DIN1055.de  |  Lastannahmen für Anwender

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Sorry,

ich bin wohl nicht in der Lage die Formate so zu übertragen das diese
den Zellenbezug zu F dynamisch mitnehmen.
Wenn ich die Zellenformate einzeln übertrage klappt es, wenn ich auf den
Pinsel einen doppelklick mache und dann den kompletten Bereich auswähle klappt es nicht.
Was ist denn mein Fehler, muss das was mit rechter Maus oder ALT oder Strg?

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hmm... Beispielmappe? Sensible Daten raus, in Beispiel.xls.txt umbenennen, und hoch damit?

------------------
DIN1055.de  |  Lastannahmen für Anwender

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Test1.zip

Einzeln kann ich übertragen aber nicht im Bereich.
Ich schaffe es nicht. Ich werfe die Testtabelle mal auf den Server.

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Test1.zip

So?

------------------
DIN1055.de  |  Lastannahmen für Anwender

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hy Frederik,

SUPER....
Problem ist gelöst,dank der Hilfe der Excel-Profis hier im Forum.

Danke und schönes Wochenende

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Test-korrigiert.xlsx.txt

 

Zitat:

---

Wenn der Liefertermin akt.Kw +1 bis +4 ist, soll die Zelle mit gelb ausgefüllt werden.
Wenn der Liefertermin akt.KW +5 +9 ist, dann Zelle grau füllen.

---

entweder eure XL rechen mit den Formeln anders als meines...
KW 32 ist grau? und was ist mit KW 10 und drüber?

Drum habe ich mal eine Mappe hoch geladen...

... irgend etwas ist eh merkwürdig, ich kann mit XL 2003, XL07 Formate öffnen und anscheinend auch speichern... 

oder ich habe zuviel gearbeitet...

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Bild02-080417.jpg

Hallo und einen schönen Tag Thomas.

Was steht denn bei dir in den Excel Optionen?
Mit x07 kannst du natürlich x03 Dateien öffnen. Nur wenn die Grungeinstellung getroffen wurden wird die Datei beim Abspeichern konvertiert. Hast du aber Speichern unter Excel 95 bis 2003 eingestellt funktioniert das ohne konvertieren.

Gruß
ThoMay

------------------
Schaut mal nach im Bereich Wissenstransfer.
Neues Forum: Schweißen, Löten, Kleben

Es gibt keine dummen Fragen, nur unzweckmäßige.
Hierauf bekommt man dann manches mal eine unzweckmäßige, freundliche Antwort.

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Ne, Tho(mas)? ich habe XL 2003

Da muss wohl irgend ein Patch drübergelaufen sein;-)

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

'n Abend,

zu Thomas' Beitrag: 

Zitat:

---

Und was ist mit KW 10 und drüber?

---

Keine Ahnung   . Ich schließe Kalenderwochen ab heute + 10 Wochen kategorisch aus - war ja nicht gefragt. Marco, Du auch?

Thomas, hast Du (oder Dein Admin in Deiner Abwesenheit   ) vielleicht dieses Werkzeug (zweiter Absatz) installiert? Würde mich interessieren...

Aha. Deine Antwort überschneidet sich mit meinem Beitrag ;-)

[Edit: Habe gerade leider kein 07 - und auch kein patch - zur Verfügung. /Edit]

Frederik

------------------
DIN1055.de  |  Lastannahmen für Anwender

[Diese Nachricht wurde von Paulchen am 19. Sep. 2008 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

KopievonTest1.xlsx.txt KopievonTest1.xls.txt

jup, ich denke auch das ein Update installiert wurde,
die Meldung diese Excelmappe wurde mit Excel 2007 erstellt,
wollte mich schon zu  einem leisen Fluch verleiten   

@Frederik, ich habe mal deine Mappe ein wenig verändert    Was fällt auf?

PS, ich will garnicht wissen was unsere Gurus alles so installieren,
kürzlich hatte ich ein Problem mit dem Treiber einer Spacemouse und der Software die darauf nicht reagierte.

nachdem ich dann online sehen konnte Wo und mit Was für Werkzeugen der alles rumfuhrwerkt hat
(dos-ebene, Regedit, diverse dll-scans, und das ganze dauerte keine 3 Minuten und es lief...
- ich mein, ich weiss zwar wirklich viel - aber meine Fresse, im Vergleich dazu bin ich dann doch stark DAU gefährdet 

2te Mappe sollte mit xl03 zu öffnen sein

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Hmm... der Reihe nach.

Zunächst mal herzlichen Dank für die Multi-Versions-Uploads. Mit 2003 lässt es sich öffnen. Wenn ich aus WEEKNUM eine KALENDERWOCHE mache, tut sich sogar was (germanisches xls).

Tja, der Jahreswechsel bricht mir das Genick . Meintest Du das? Alternative: Immer vor Weihnachten alles brav löschen - oder einen Zähler einbauen, der 52 KWs berücksichtigt. Obacht: Schaltjahre, da können es schon mal 52,5 werden. Und negative Kalenderwochen sind natürlich genauso unsinnig wie negative Uhrzeiten. Damit mag ich mich aber nicht befassen.

Würde vorschlagen, wir warten mal ab, was der Fragesteller dazu meint?

Manchmal sind mir die DAUs die lieberen Menschen, weil ich sie besser verstehe...
Frederik

------------------
DIN1055.de  |  Lastannahmen für Anwender

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Test-korrigiert.xls.txt

ne, an das Jahreswechselproblem habe ich noch garnicht gedacht   

In Spalte G (KopievonTest1.xls.txt) habe ich die Zellen mal händisch
so formatiert wie es im Eingangsthread gewünscht war
und irgendwie haut das mit den Bedingungen nicht so hin.

Das Marco so begeistert ist und ich ein wenig verwundert dreinschau,
lässt mich ein wenig zweifeln ob mein XL doch ein wenig anders sich verhält.;-)

Drum habe ich auch die test-korrigiert.xlsx.txt hochgeladen.

Hier nochmal in der xl03 version.

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Deine Bedingung 3 ist auch gelb, obwohl sie grün sein sollte? KopievonTest1.xls.txt, versteht sich. Der Text in Zelle F21 lautet grün, gefärbt ist sie (händisch?) gelb. War das bei meiner xlsx in Test1.zip auch schon so?

------------------
DIN1055.de  |  Lastannahmen für Anwender

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Moin,

ich habe nach der ersten Version leider das Büro fluchtartig zum Kunden verlassen und bekam da noch eine ordentliche Standpauke.
Nach diesem Konzert bin ich dann total frustriert nicht mehr ins Büro aber mir ging auch noch die Frage durch den Kopf: Was ist mit dem Jahreswechsel?
Wenn akt. KW = 50
Eine KW 59 habe ich in meinem Kalender auch nicht gefunden. Wenn ich die letzte Version vom Server lade habe ich auch nicht die geünschte Farbcodierung. Das ist aber die Bedingte Formatierung,
wenn ich in A15 bin so habe ich bei der Formatierung wieder den Hinweis aus F4 und nicht auf F15 und das ist das was ich immernochnicht verstehe.

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Kurze zwischenbemerkung:

Bezieht sich in Spalte F der Wert für Kalenderwoche auf ein Datum (in einer anderen Spalte zb.),
oder existiert nur händisch die Zahl für die Kalenderwoche.

wenn letzteres braucht man noch das Jahr der Kalenderwoche (als zusätzliche Spalte) für den Jahreswechsel

Wenn ersteres, dann könnte man es berechnen (Jahreswechsel)

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Ich will mich nicht auch noch reinhängen, nur anmerken, dass Excel (zumindest früher) die Kalenderwoche nicht korrekt bestimmen konnte (bzw. kann). Es gibt einen Lösungsansatz per VBA-Funktion, den ich hier schon mal erwähnte.

So, dann schaue ich mal wie ihr den Jahreswechsel vorverle... äh, das Dings, nah... Problem löst 

------------------
Seit Pro/E Version 1 dabei, auwei...

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Der Wert aus Spalte F kommt aus einem (veralteten) PPS-System ohne
Jahresangabe. Mit einem Reportgenerator bekomme ich ein Excelblatt mit den entsprechenden Informationen.

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Test4_xls.txt

Moin,

kann die Problematik mit dem Jahreswechsel so gelöst werden?

Habe mal ne Version mit Excel2002 hochgeladen. Bis KW52 klappt das Ding, dann verstehe ich die Welt nicht mehr, und ab KW9(zeilen erweitern) ist wieder alles i.O.!
Ob es eine KW 53 gibt oder nicht sollte nicht so wichtig sein, kleine Ausnahmen bestätigen die Regel.....
------------------
Gruß
    Marco

[Diese Nachricht wurde von thewolff am 29. Sep. 2008 editiert.]

[Diese Nachricht wurde von thewolff am 30. Sep. 2008 editiert.]

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Test5_xls.txt

Hallo zusammen,

ich habe die Terminliste mit einer Hilfspalte versehen und dann ist die Farbmarkierung
so wie ich es benötige (Ausnahme ist KW53).
Bitte mal testen.

------------------
Gruß
    Marco

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Test-korrigiertmit_jahreswechselberuecksichtigung.zip

Deine Lösung funktioniert!

ich habe eine ähnliche -etwas aufwändigere ;-) da ich die Amerikanische Kalenderwoche nicht mag ;-)
und ich wollte sich noch nicht hochladen, da ich mit der Lösung nicht zufrieden war -mehrfach formatierungen bei mehreren Jahrenlaufzeit.
Das lässt sich aber nur beseitigen wenn die KW auch ein Jahr bekommt ,)

Namen vergeben:

Code:

---

Dinkw    ('des Aktuellen Jahres)=KÜRZEN((HEUTE()-DATUM(JAHR(HEUTE()+3-REST(HEUTE()-2;7));1;REST(HEUTE()-2;7)-9))/7)
SovorKW1 ('des nächsten K-Jahr) =DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCHENTAG(DATUM(JAHR(HEUTE())+1;;);3))-1

---

DinKW um die aktuelle Woche basierend auf =(heute() und SovorKW1 um die letzte KW des Aktuellen Jahres zu definieren.

bedingte Formatierungen:

Code:

---

grau = '=ODER(UND($F4-$A$1>=5;$F4-$A$1<=9);UND($F4-($A$1-$J$1)>=5;$F4-($A$1-$J$1)<=9))
gelb = '=ODER(UND($F4-$A$1>0;$F4-$A$1<=4);UND($F4-($A$1-$J$1)>0;$F4-($A$1-$J$1)<=4))

---

Da die zu Kontrollierenden KW nur als Zahl vorliegen,
ist keine eindeutige Zuordnung über mehrere Jahre möglich,
das heisst, alles was in dieses Raster rein fällt wird farblich hervorgehoben.

Die Ermittlung der letzten Kalenderwoche des jeweiligen Jahres soll dazu dienen ob das Jahr 52 oder 53 Wochen hat.

Ich brauchte auch eine weitere Hilfszelle J

Code:

---

=KÜRZEN((SovorDINKW1-DATUM(JAHR(SovorDINKW1-4-REST(SovorDINKW1-2;7));1;REST(SovorDINKW1-2;7)-9))/7)

---

zur Ermittlung der letzten KW des Jahres...  :( eigentlich wollte ich es ohne

die restlichen Testzellen können gelöscht werden, die dienten nur diversen tests ;)

'latürlich hätte man auch ohne Hilfzelle auskommen können:

Code:

---

grau = '=ODER(UND($F4-$A$1>=5;$F4-$A$1<=9);UND($F4-($A$1-KÜRZEN((DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCHENTAG(DATUM(JAHR(HEUTE())+1;;);3))-1-DATUM(JAHR(DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCH  ENTAG(DATUM(JAHR(HEUTE())+1;;);3))-1-4-REST(DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCHENTAG(DATUM(JAHR(HEUTE())+1;;);3))-1-2;7));1;REST(DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCHENTAG(DATUM(JAHR(H  EUTE())+1;;);3))-1-2;7)-9))/7))>=5;$F4-($A$1-KÜRZEN((DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCHENTAG(DATUM(JAHR(HEUTE())+1;;);3))-1-DATUM(JAHR(DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCHENTAG(DA  TUM(JAHR(HEUTE())+1;;);3))-1-4-REST(DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCHENTAG(DATUM(JAHR(HEUTE())+1;;);3))-1-2;7));1;REST(DATUM(JAHR(HEUTE())+1;1;7*1-3-WOCHENTAG(DATUM(JAHR(HEUTE())+  1;;);3))-1-2;7)-9))/7))<=9))

---

aber welcher Normalsterblicher tut sich dieses an [naja, vielleicht lässt die sich ja noch kürzen]  ,mal abgesehen davon, Excel 03 lässt nicht so viele Verschachtelungen zu -in Xl07 wohl möglich 

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

Der Vollständigkeit halber ein Hinweis auf Excelformeln.de - normkonform und nicht ganz so lang wie Thomas' Formelmonster ;-).

------------------
DIN1055.de  |  Lastannahmen für Anwender

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP

---

das Formelmonster behandelt ja auch nicht die DinKW ;-)
die steht ja weiter oben unter vergebene Namen Dinkw und ist nicht lang,
bzw. entspricht der Formel auf Excelformeln ;-)

Eine Antwort auf diesen Beitrag verfassen (mit Zitat/Zitat des Beitrags) IP