Bedingte Formatierung von einer Zelle auf eine andere spiegeln, sobald sie aktiv ist

Question

Bedingte Formatierung von einer Zelle auf eine andere spiegeln, sobald sie aktiv ist

Anonym

Hallo zusammen,

ich komme leider nicht weiter und hoffe, dass mir hier jemand helfen kann.

Ich habe eine umfangreiche Excel-Tabelle mit einigen bedingten Formatierungen.

So auch diese drei:

Anwenden auf: Q4:Q1048576; AK4:AK1048576; AQ4:AQ1048576; AW4:AW1048576; BC4:BC1048576; BI4:BI1048576; BO4:BO1048576; BU4:BU1048576; CA4:CA1048576 --- Zellen hervorheben mit Datum von Nächsten Monat --- benutzerdefiniertes Format: Rot, Fett ---

Anwenden auf: Q4:Q1048576; AK4:AK1048576; AQ4:AQ1048576; AW4:AW1048576; BC4:BC1048576; BI4:BI1048576; BO4:BO1048576; BU4:BU1048576; CA4:CA1048576 --- Zellen hervorheben mit Datum von Diesen Monat --- benutzerdefiniertes Format: Rot, Fett ---

Anwenden auf: Q4:Q1048576; AK4:AK1048576; AQ4:AQ1048576; AW4:AW1048576; BC4:BC1048576; BI4:BI1048576; BO4:BO1048576; BU4:BU1048576; CA4:CA1048576 --- Zellen hervorheben mit Datum von Letzten Monat --- benutzerdefiniertes Format: Rot, Fett ---

Nun möchte ich, dass sobald in einer der angegebenen Bereiche ein Datum in Rot und Fett ausgegeben wird, sich der Name, der sich bei mir in Spalte A befindet, auch Rot und Fett dargestellt wird, damit ich auf den ersten Blick sehe, "in dem Fall ist Handlungsbedarf".

Ich dachte zunächst an eine einfache Weiterleitung der Formel à la =$R4= ... Allerdings bezieht sich die Formel ja auf eine andere Formel und nicht (wie bei der Feldfärbung) auf einen Textinhalt ("Ho"). Dann kam mir eine Wenn-dann-Formel in den Kopf, aber auch da bin ich irgendwie blockiert.

Hat jemand neue Impulse für mich oder einen Lösungsweg?

Ursprünglich hatte ich auch geplant, bei den obigen drei bedingten Formatierungen zum Datum anders vorzugehen. Es wäre schön gewesen, wenn mir die gewünschte Formatierung ausgegeben wird, sobald das eingegebene Datum 1 Monat in der Zukunft liegt, auf den Tag genau liegt oder aber in der Vergangenheit. Da ich da schon nicht weiter kam, hab ich die Option mit den Monaten (letzte, diese, nächste) gewählt.

Viele Grüße und bereits jetzt Danke für die Hilfestellungen!

Gesperrte Frage. Diese Frage wurde aus der Microsoft-Support-Community migriert. Sie können darüber abstimmen, ob sie hilfreich ist, aber Sie können keine Kommentare oder Antworten hinzufügen oder der Frage folgen.

0 Kommentare

Antwort, die vom Frageautor angenommen wurde

Andreas Killer 144.1K Volunteer Moderator

f) Es soll keine Unterschiede in der Farbe geben. Es ist egal ob in der Zeile vielleicht zwei oder drei Daten stehen die im letzten oder nächsten Monat liegen. Sobald ein Datum, ganz egal welches, formatiert ist soll der Name in Spalte A ebenfalls formatiert sein damit man weiß "da muss ich mal schauen, da läuft demnächst ne Hilfe aus"

Okay, damit man ich was anfangen.

Die Berechnung ist im Prinzip recht einfach, wenn die Farbe immer gleich sein kann, dann können wir alle 3 BF (bedingte Formatierung: Datum ist im letzten/diesen/nächsten Monat) zu einer zusammenfassen.

Gehen wir mal von einem Datum im Februar 2025 aus, dann müssen wir prüfen ob dieses Datum vom 1.1.2025 bis 31.3.2025 liegt. Wenn dem so ist, dann Zelle bunt machen. Das ist schon alles.

Jetzt muss man wissen das eine BF eine Zelle bunt macht wenn die verwendete Formel ein boolsches Ergebnis, also WAHR liefert. Gibt sie FALSCH zurück bleibt die Zelle wie sie ist.

Es gibt keinen Operator "zwischen" in Excel, daher müssen wir 2 Vergleiche machen und die Ergebnisse mit einem UND kombinieren.

Erstmal auf deutsch:

Ist das heutige Datum größer oder gleich dem 1.1.2025 und ist das heutige Datum kleiner oder gleich dem 31.3.2025?

Nun auf Excel:

Das heutige Datum kriegen wir mit HEUTE() und der 31.3.2025 ist das Monatsende des nächsten Monats, also =MONATSENDE(HEUTE();1)

Das erste zu prüfende Datum steht in Q4, weil wir eine Zelle in der Zeile aber in einer anderen Spalte formatieren wollen muss die Spalte absolut sein und der Vergleich ist =$Q4<=MONATSENDE(HEUTE();1)

Den 1.1.2025 könnten wir auch berechnen, aber wir sind da mal faul und nehmen =MONATSENDE(HEUTE();-2) was uns den Tag davor liefert. Der zweite Vergleich ist nun =$Q4>MONATSENDE(HEUTE();-2)

Und beides kombiniert:

=UND($Q4>MONATSENDE(HEUTE();-2);$Q4<=MONATSENDE(HEUTE();1))

Soweit, so gut, jetzt müssen wir aber noch andere Zellen prüfen die ebenfalls ein Datum beinhalten, also nochmal auf deutsch: Wenn Q4 im letzten, diesen oder nächsten Monat liegt oder AK4 im letzten, diesen oder nächsten Monat liegt, dann Zelle bunt machen.

Nun auf Excel:

=ODER(

UND($Q4>MONATSENDE(HEUTE();-2);$Q4<=MONATSENDE(HEUTE();1));

UND($AK4>MONATSENDE(HEUTE();-2);$AK4<=MONATSENDE(HEUTE();1))

)

Und dieses Verfahren kan man nun für alle weiteren Spalten ebenso anwenden und die Formel erweitern.

So lange Formeln lassen sich schlecht in die BF eingeben, ich schreibe sie immer in eine Zelle rechts neben die Daten und dann kann man sie auch mal runter und nach rechts ziehen und schauen ob die Ergebnisse stimmen. Das macht Excel im Prinzip auch wenn man die BF auf einen Bereich anwendet.

Alles klar?

Andreas.

0 Kommentare

5 zusätzliche Antworten

Answer 1

Anonym

Hola,

steht über den Datumswerten in Zeile 1 immer "WV"?

Das taggenaue Datum bekommt man mit Edatum() hin. =edatum(heute();1) bzw. =edatum(heute();-1)

Gruß,

steve1da

0 Kommentare

Answer 2

Hola,

steht über den Datumswerten in Zeile 1 immer "WV"?

Das taggenaue Datum bekommt man mit Edatum() hin. =edatum(heute();1) bzw. =edatum(heute();-1)

Gruß,

steve1da

Hey steve1da,

danke für deine Antwort! In Zeile 1 steht nicht immer WV. Dies ist die einzige Zelle wo WV steht, in den anderen steht "befristet bis".

Danke für den Input, ich schaue mir die Formeln mal genauer an. Vllt kriege ich es ja hin.

LG

Answer 3

a) Eine bedingte Formatierung auf 9.437.157 Zellen anzuwenden ist eine ganz dumme Idee.

Du wirst es niemals in der Größenordnung brauchen und wenn mit der Datei gearbeitet wird, dann "zerschreddert" sich der Bereich durch einfügen/löschen/verschieben in in immer mehr Teilstücke und die Datei wird größer und langsamer und irgendwann unbrauchbar.

b) Auch diese Formatierung wendet eine Formel an um zu ermitteln ob die Zelle eingefärbt werden soll oder nicht. D.h. Du kannst auch selber diese Formel vorgeben.

c) Die Bereiche liegen soweit auseinander, wenn da irgendwo was bunt wird wirst Du das nicht sehen.

d) Wenn der Name in Spalte rot werden soll, dann braucht es eine bedingte Formatierung die in der Zeile in alle Zellen mit einem Datum schaut was da los ist.

e) Da ein Datum IMMER eine Deiner Bedingungen erfüllt (Datum ist vom letzten/diesen/nächstem Monat) hast Du schon mal ein Problem.

f) Welche Farbe soll es denn sein wenn in der Zeile ein Datum von letzten Monat UND eines in diesem Monat UND eines im nächsten Monat steht?

Konzepte dieser Bauart kann man machen wenn man alle Daten auf dem Bildschirm sehen kann oder im Höchstfall einmal scrollen muss.

Zum anderen gibt es dabei ein Problem wenn man in der Zukunft nochmal in eine ältere Datei schaut, dann ist durch diese Art bedingte Formatierung entweder alles bunt oder nichts mehr. Für "flüchtige Daten" die eh nach einer gewissen Zeit (spätestens nach einem Jahr) verworfen / gelöscht werden kann man das machen... ich denke mal das wird bei Dir nicht der Fall sein.

Um das alles zu bewerkstelligen braucht es ein Konzept das die Daten in Tabellen speichert, diese kann man dann auswerten und über Filter die entsprechenden Daten anzeigen.

Andreas.

Answer 4

Hallo Andreas,

danke, dass du dir die Zeit genommen hast mir so ausführlich zu antworten.

Ich versuche vieles selbst zu erlernen und weiter zu entwickeln in der Datenbank, komme aber manchmal auch an meine Grenzen und bin dann froh über Input von außerhalb. Bisher wurde ich über Google oft fündig, aber jetzt stehe ich in einer Sackgasse. Manchmal reichen ja neue Denkanstöße.

a) Wenn ich die komplette Spalte (abzüglich Zeile 1-3) markiere, spuckt er mir automatisch Zeile 1048576 aus, daher habe ich das so übernommen. Deine Anmerkung bringt für mich aber jetzt Licht ins Dunkel. Ich habe mich schon gewundert, warum so viele bedingte Formatierungen teilweise doppelt mit nur einzelnen Zellen angelegt sind. Ich überarbeite das, danke!

b) Die aktuelle Formatierung in Spalte Q bezieht sich ja auf keine Formel, sondern auf "Datum von". Mir erschließt sich aber nicht, wie ich in einer Formel in Spalte A die Option "Datum von" in eine Formel integriere.

c) Das ist nicht schlimm, der Bereich der auf dem Bildschirm zu sehen ist, gibt nur einen ersten Überblick. Für Details müssen die Kollegen, die damit arbeiten ohnehin scrollen. Aber dann wäre es halt wichtig, dass die Daten ins Auge fallen, da die Liste aufgrund der Datenmenge einfach nur noch nach Buchstabensalat ausschaut.

d) Guter Hinweis, die ganze Zeile habe ich so nie betrachtet. Macht aber Sinn!

e) Das Datum erfüllt nicht immer eine meiner drei Bedingungen. Manchmal liegt das Datum auch 6,7,(...),12 Monate im Voraus. Dann würde die Bedingung nicht greifen, was genau so gewollt ist. Denn wenn eine Hilfe bis 02/2026 bewilligt wurde, dann brauch sie uns heute noch nicht ins Auge springen. Dann möchte ich erst ab Januar 2026 eine Abhebung von den anderen Daten mithilfe der Formatierung haben.

f) Es soll keine Unterschiede in der Farbe geben. Es ist egal ob in der Zeile vielleicht zwei oder drei Daten stehen die im letzten oder nächsten Monat liegen. Sobald ein Datum, ganz egal welches, formatiert ist soll der Name in Spalte A ebenfalls formatiert sein damit man weiß "da muss ich mal schauen, da läuft demnächst ne Hilfe aus"

Ich würde die Daten doch eher als "flüchtige" Daten bezeichnen, in der Tabelle herrscht eine hohe Fluktiation. Sobald eine Hilfe beendet wird, kommt diese (mit deutlich abgespeckten Infos und ohne bed. Formatierungen) in eine extra Archiv-Datei. Länger als 2-3 Monate liegt kein Datum in der Vergangenheit und länger als 18 Monate nicht in der Zukunft.

Vielen Dank für deine Anmerkungen!

Ich werde erst am Montag wieder antworten, schönes Wochenende! :)