Bedingte Formatierung Fehler durch Formel in Zelle unterbinden

[M_King]

Hallo Spezialisten,
 
gestern habe ich eine super Lösung für meine Zeiterfassungstabelle erhalten. Die folgende Formel für eine bedingte Formatierung:
 

Code:

=ODER(UND(L12>=9,75/24;F12<0,75/24);UND(L12>=6,5/24;F12<0,5/24))

 
Diese bewirkt, dass das Pausenfeld (Spalte F) rot wird wenn bestimmte Anwesenheiten erreicht sind.
 
Jetzt hat sich ein kleines Problem ergeben. In den Zellen der Spalte L ist eine Formel hinterlegt. Wenn nun beispielsweise an einem Sonntag keine Arbeitszeiten eingetragen werden (welche die Zelle L12 speisen), dann wird das Pausenfeld (Spalte F) ebenfalls rot formatiert. Dies hängt eindeutig mit der hinterlegten Formel zusammen, denn wenn ich diese testweise lösche ist alles wieder perfekt. Kann man die Formel irgendwie erweitern um das zu unterbinden? Ich wäre sehr dankbar für eine Lösung.
 
Was ich auch nicht verstehen, wenn ich die bedingte Formatierung kopiere dann bleiben die Bezüge fest auf Zeile 12, obwohl ja keine relativen oder absoluten Bezüge in der Formel stehen? Weiß jemand Rat?
 
Eine Antwort würde mich sehr freuen!
 
Vielen Dank und beste Grüße
 
M_King

[steve1da]

Hola,

du musst nicht für jede Zelle eine bed. Formatierung anlegen. Einfach den Bereich markieren für den sie gelten soll und dann einmalig folgende Formel eingeben:

Code:

=ODER(UND(L9>=9,75/24;F9<0,75/24;L9<>"");UND(L9>=6,5/24;L9<0,5/24;L9<>""))

In L16 lässt du im ersten Fall ein Leerzeichen an Stelle einer leeren Zelle ausgeben. Ist das gewollt?

In der bed. Formatierung haben alle Zellen die gleiche Formel hinterlegt. Excel regelt das über den Bereich bei "wird angewendet auf".

Gruß,
steve1da

[M_King]

Hallo Steve1da,


leider macht die neue Formel nicht was sie soll:

Wenn die IST Anwesenheit (L12) zwischen 6:30 Std. und 9:44 Std. liegt, müssen mindestens 30 Minuten Pause (F12) eingetragen sein, andernfalls soll das Feld rot formatiert werden.
 
Wenn die IST Anwesenheit (L12) größer als 9:44 Std. beträgt, müssen mindestens 45 Minuten Pause (F12) eingetragen werden, andernfalls soll das Feld ebenfalls rot formatiert werden.

Bei der ursprünglichen Formel geht das, allerdings sind die Felder rot wenn keine Zeiten eingeben sind. Der Hinweis mit dem Leerzeichen ist interessant und hat vielleicht etwas damit zu tun?

Irgendetwas stimmt da noch nicht...

Danke für weitere Hinweise.

Schöne Grüße



M_King

[MisterBurns]

Du hast nicht genau gelesen, was Steve dir geschrieben hat!

In L16 lässt du im ersten Fall ein Leerzeichen an Stelle einer leeren Zelle ausgeben. Ist das gewollt?

Schau dir in Spalte L deine Formel genau an. Was fällt am ersten Wenn-Dann auf? Was passiert, wenn du das änderst?

[M_King]

Hmmm...? Irgendwie raffe ich heute gar nichts mehr da ich schon seit Stunden auf diese Tabelle schaue und es einfach nicht hinbekomme.

Fakt ist, deine Formel macht genau was sie soll, das ist schon mal Top! Wenn ich das Leerzeichen rausnehme ändert sich aber nichts. (Jedenfalls nichts offensichtliches)Die Zelle bleibt weiterhin rot wenn vorne keine Zeiten eingetragen sind...(Das komische ist, wenn ich dir Formel von Steve nehme, und das Leerzeichen rausnehme, dann ist das Feld nicht mehr rot, aber leider macht die Formel nicht was sie soll???

Und zu guterletzt bleiben auch die Bezüge alle auf derselben Zeile, auch wenn ich das in einem Rutsch mit markieren einfüge... :22:

Hab erbarmen... :17:

[MisterBurns]

F9:F39 markieren - bedingte Formatierung --> alle bedF löschen, die du mit dieser Formel hinterlegt hast
Und dann einmal die Formel neu eingeben. Wie Steve bereits geschrieben hat, reicht die einmalige Eingabe für alle markierten Zellen.

In Spalte L gehört natürlich auch überall das Leerzeichen raus.

[steve1da]

Hola,

Und zu guterletzt bleiben auch die Bezüge alle auf derselben Zeile, auch wenn ich das in einem Rutsch mit markieren einfüge.

In der bed. Formatierung haben alle Zellen die gleiche Formel hinterlegt. Excel regelt das über den Bereich bei "wird angewendet auf".

Les dir morgen nochmal alles in Ruhe durch, dann läuft die Tabelle wie gewünscht.

Gruß,
steve1da

[M_King]

Leute, vielen Dank! Jetzt hab ich es geschnallt und es läuft!

In der Formel von Steve war hinten ein L statt ein F, daher lief das nicht.

Mit dem "einmaligen" Einfügen klappt auch, mich hat der Bezug irritiert, aber wie ihr ja gesagt habt, das wird automatisch geregelt... - und so ist es...

Klasse! Wünsche euch ein schönes Wochenende, VG Martin

[MisterBurns]

In der Formel von Steve war hinten ein L statt ein F, daher lief das nicht.

Das L ist aber anhand deiner Beispieldatei korrekt.

[M_King]

Das schon, aber dass davor... (quasi das in der Mitte) muss ein F sein. Vergleich mal deine Formel (an der er sich orientiert) und seine... Einfach ein Tippfehler.

Aber Danke..