XL(2000) Bedingte Formatierung wirkt nicht - Bug oder Feature

Question

Hallo!

Ich erinnere an diesen Fall: https://supportnet.de/t/2409077

Durch die Idee inspiriert wollte ich die aktuelle KW in Form der 7 Spalten die sie einnimmt im fixierten Tabellenkopf hervorheben lassen.

Eine Hilfszelle berechnet die aktuelle KW (würde das sogar gerne umschiffen wollen) und mit etwas Mathematik (nur in der jeweils ersten der 7 Spalten ist die KW vermerkt) bekomme ich es leicht hin, dass, über WENN angezeigt und damit bestätigt, die Bedingung korrekt für jede Spalte der ermittelten Woche zutrifft - Ja oder Nein.

Was nicht gelingen will: Leere Zellen (ja nicht mal die mit der Formel [Vorwochenwert + 1]) dazu zu bringen ihren Hintergrund zu verändern. Bei den Testzellen mit WENN klappt das einwandfrei; aber da lasse ich auch ein ‹+› oder ‹-› erscheinen [Spaltenbreite ist zu eng um Wahr/Falsch darzustellen].

Die Kopfzeilen sollen leer bleiben (bis auf die von Montag die die Wochennummer liefert und auf die ich zurückgreife).
Woran liegt das? Ist das ein Bug oder ein Feature, dass nur irgendwie befüllte Zellen [Montag..??] eine bedingte Formatierung «triggern» oder kommt hier der Effekt der fixierten Zeilen irgendwie zum tragen? Mir ist keine Logik aufgefallen.

Eric March


PS: Es soll eine Bequemlichkeit sein; und Makros sind in dieser Mappe nicht erlaubt. Weiterhin wird je neuer Monat [je Monat ein Blatt das alle Wochen die dieser Monat berührt in Gänze hält] das Blatt kopiert, die Übergangswoche [bzw. die letzte] erhalten und dahinter so viele Wochen per Copy-Paste, dass manches so übertragbar ist, manches andere dummerweise nicht. Das tun Makros (von außerhalb), aber ich möchte so viel es geht über Copy-Paste transportieren und so wenig wie möglich per Makro anpassen; laufende Tage etwas die zwar =…+1 benutzen, aber an jedem Kaland natürlich eine feste 1 brauchen, die 2 macht dann aber die mitkopierte Formel usw. usf. Teile der Spalten übertragen so weitere Bedingte Formatierungen, das wollte ich ausnutzen.

Answer 1

Hallo Eric,

so richtig blick ich da nicht durch, wie das bei dir aufgebaut ist, aber grundsätzlich ist es machbar, dass in einem Monats-Kalender der Kopf der Spalten für die Tage der aktuellen Woche über eine bedingte Formatierung optisch hervorgehoben wird.

Ich kann auch den Sinn nicht nachvollziehen, weshalb in den Zellen außerhalb der aktuellen Woche in einem Monats-Kalender nichts angezeigt werden soll, aber auch das ist zumindest optisch möglich, wenn Schrift und Hintergrund die gleiche Farbe haben.

-> Guckst du

Wenn du dein Rechner-Datum mal testweise auf eine andere Juni-Woche umstellst, ändert sich die Markierung in der Tabelle Juni 2012 (1) entsprechend. In der Tabelle Juni 2012 (2) werden zusätzlich noch alle Einträge außerhalb der aktuellen Woche durch eine weiße Schrift 'unsichtbar' gemacht und auch die sonstigen Felder für die aktuelle Woche mit einer Hintergrundfarbe markiert.

Und eine Hilfszelle für die aktuelle Woche ist dafür auch nicht erforderlich.

Wie gesagt, so richtig hab ich dein Anliegen nicht kapiert, aber eventuell kannst du wenigsten mit einem Teil der in den o.a. Tabellen verwendeten bedingten Formatierungen was anfangen.

Gruß
Kalle

Answer 2

grundsätzlich ist es machbar, dass in einem Monats-Kalender der Kopf der Spalten für die Tage der aktuellen Woche über eine bedingte Formatierung optisch hervorgehoben wird.
Bei mir will es nicht gelingen eine nicht durch eine händische Eingabe belegte Zelle per Bedingter Formatierung mit einer Hintergrundfarbe zu versehen.
Das das im Handstreich gehen müsste ist auch meine Vorstellung. Muss vielleicht die Zelle auf eine sich neutralisierden Weise Teil der Bedingungsformel sein?

Ich kann auch den Sinn nicht nachvollziehen, weshalb in den Zellen außerhalb der aktuellen Woche in einem Monats-Kalender nichts angezeigt werden soll, aber auch das ist zumindest optisch möglich, wenn Schrift und Hintergrund die gleiche Farbe haben.
Den alten Trick kenne ich natürlich und wollte ihn für die Hilfszelle die die Woche liefert verwenden.
Zur Verdeutlichung: In U1 mag die 2. Woche beginnen. Dann stehen in U2..AA2 die Tage, sagen wir 3,4...9. In V1 bis AA1 soll aber nichts stehen. Die spannenden Daten kommen dann ab der 6. Zeile. U1 ist eine Formel die auf N1 mittels +1 zugreift
Das, und die Tatsache, dass die erste Woche mit einem 27. beginnt ist die Mustermappe so leider nutzlos. Ich muss gewachsenen Strukturen jonglieren…

Wenn du dein Rechner-Datum mal testweise auf eine andere Juni-Woche umstellst,
Da kann ich außerhalb mit Werten wunderbar testen, das klappt alles - nur die Formatierung rührt sich kein bisschen.

Und eine Hilfszelle für die aktuelle Woche ist dafür auch nicht erforderlich.
Da sie nur einmal je Formatirungs-String vorkommt ist das tatsächlich nicht nötig. Es per Hilfszelle zu machen ist Programmiergewohnheit.

Wie gesagt, so richtig hab ich dein Anliegen nicht kapiert, aber eventuell kannst du wenigsten mit einem Teil der in den o.a. Tabellen verwendeten bedingten Formatierungen was anfangen.
Siehe die folgende Frage..:

Also zusammengefasst:
Wie bekomme ich die Bedingte Formatierung dazu in einer leeren (bzw. mit Formel belegten) Zelle anhand von Zuständen außerhalb dieser Zelle zu wirken?

Eric March

Answer 3

Hallo Eric,

Wie bekomme ich die Bedingte Formatierung dazu in einer leeren (bzw. mit Formel belegten) Zelle anhand von Zuständen außerhalb dieser Zelle zu wirken?

Hast du dir die verwendeten Formatierungen nicht angesehen? In der zweiten Tabelle beziehen sich doch z.B. die bedingten Formatierungen des Bereiches B3 bis AE10 auf 'Zustände außerhalb dieser Zellen', nämlich auf die Datumsangaben in B2 bis AE2, welche daraufhin überprüft werden, ob sie in die aktuelle Kalenderwoche fallen, oder nicht. Die Zellen B3:AE10 sind zwar im Beispiel alle belegt, aber auch wenn du deren Inhalt löschst, hat das keinen Einfluss auf deren bedingte Formatierung. Du könntest den Anwendungsbereich einer Regel auch in Bereiche der Tabelle ausweiten, wo absolut nichts drin ist, z.B. von $B$3:$AE$10 auf $B$3:$AE$10000, dann würden sie auch auf den Bereich bis Zeile 10.000 angewendet. Man muss lediglich darauf achten, dass in der Formel selbst die Bezugszeile 'festgeklopft' wird, wie im Beispiel mit B$2 statt einfach nur B2 geschehen.

Oder hab ich dich schon wieder missverstanden?

Excel setzt bei den bedingten Formatierungen sinnigerweise auch Formeln mal gerne in Anführungszeichen, was sie dann unbrauchbar macht. In dem Fall muss man die Regel nochmal 'bearbeiten' und die Anführungszeichen entfernen. Eventuell liegt auch hier ein Teil deiner Probleme.

Gruß
Kalle

Answer 4

Den Hinweis auf die "" will ich mal für alle 'rausstreichen - das ist eine wirklich lästige Macke. Ich habe die Dinger natürlich rausgeworfen.

Wenn es offenbar geht (eigentlich erwarte ich auch nichts anderes) muss ich rausfinden warum sie diese Mappe so sehr sperrt.

Aber es kommt noch besser:
In einem Textblatt mag A1 mal 19 enthalten, A2 die 25. In E26 und E27 steht: ((A1>19)*1)<>0 bzw. ((A2>19)*1)<>0 und die Wahrheitswerte stimmen. Trage ich dort stattdessen ((QUOTIENT(A1;3)>7)*1)<>0 ein kommt ebenfalls brav ein Wahr. Verwende ich nun die selben Bedingungen bei der Bedingten Formatierung klappt die erste Formel wunderbar. (Und, he, es formatiert auch :-)) ) Nehme ich die zweite kriege ich eine Fehlermeldung, dass „Bezüge auf andere Tabellen oder Arbeitsmappen nicht in Bedingungen erlaubt“ wären.
Hallo??? Was für ein Kraut hat XL denn da geraucht? Eine Modulo-Berechnung löst die Palastrevolution aus?

Tatsächlich geplant ist:
=((($Q1+QUOTIENT(SPALTE()-17;7))*1)=(1*KALENDERWOCHE(HEUTE();2)-1))<>0
Q1 [Q&#8776;17] ist zz. statisch die 22, also die Woche zwischen Mai und Juni. X1 wäre Q1+1&#8801;23 usw. Der Teil mit Quotient liefert für jeden Tag korrekt die Woche aufbauend auf Q1(der erste Montag). Wenn die Zahl mit der zurechtgebauten DIN-Woche übereinstimmt wird das Ganze Wahr (also ungleich Null).
Allein der o.g. Aussetzer sabotiert das an der Wurzel. Wenn das Konstrukt stimmt müssen wir wohl die Jagd auf einen anderen Bösen Geist umlenken!?

Eric March

Answer 5

Hallo Eric,

dann lade mal besser eine Beispiel-Tabelle hoch, dieses scheibchenweise noch Dies und noch Das bringt doch nichts, am Allerwenigsten dir. Man sollte schon irgendwie nachvollziehen können, was du eigentlich womit erreichen willst und wie das ggf. die Möglichkeiten einschränken könnte.

So verstehe ich z.B. überhaupt nicht, weshalb in einem Monatsblatt der erste Montag einer KW irgendeine Rolle spielen muss, außer eventuell für das Markieren einer bestimmten oder der aktuellen Woche? Nur dazu ist doch dieser ganze Aufwand mit Quotient & Co. nicht erforderlich.

Ich hab mal in -> Beispiel 2 noch eine Tabelle Juni 2012 (3) erstellt. Dort muss lediglich in A2 das Datum für Monat/Jahr eingegeben werden. Die Kalendertage und deren dazugehörigen Wochentage tragen sich selbst in die Tabelle ein, der nicht zum angegebenen Monat gehörige 31. Tag (Spalte AF) wird ausgeblendet bzw. die Schrift weiß und die aktuelle Woche hervorgehoben, sofern sie in den Monat lt. A2 fällt.

Gruß
Kalle

Answer 6

Die Tabelle sind Firmendaten - und ich kann und will sie auch nicht als Muster hochladen da das nichts an meine Beschreibungen ändert oder verbessert. Falls die Datei beschädigt ist - dann sitze ich so weit in der Tinte und kann es nicht ändern.

Was für eine XL.Version verwendest du, was ist eingestellt? Da wird nichts Rot/Grau was mit dieser Woche zu tun hat. Aus Neugeier löschte ich in Zeile 1 mal die erste Bedingung und bekomme glatt wieder die Meldung mit den Bezügen auf andere Tabellen die nicht vorkommen dürfen um die Ohren. Kannst du das erklären?!?

Im weiteren liefert KALENDERWOCHE(HEUTE();2) für den heutigen Montag die Woche 25 und nicht 24 - so viel zu dieser Funktion.

An der Mappe ist nichts zu ändern, ich kann nur noch mal beschreiben, dass sich der für mich relevante Teil ab Spalte 17|Q in der ersten Zeile abspielt und als Erschwernis die dort beginnende Woche zu 7 Spalten die Woche 22 ab dem 28.5. ist. Definitionsgemäß sind nur in den Montagsspalten über +1 die jeweiligen laufenden Wochen aufgeführt (bis Woche 26 mit Sonntag, 1. Juli - Spalte 51|AY).
Damit «die Woche» - also sämtliche 7 Zellen in Zeile 1 die diese Woche ausmachen - ›aufleuchtet‹ muss man also die Wochennummer kennen, die Aktuelle Spalte und ob diese in der 7er-Gruppe steckt die ab der Startwoche (Spalte 17) zu berechnen ist und mit der jetzigen Wochennummer harmoniert. Alle diese Berechnungen existieren bereits (als Funktionstest):

=WENN(($Q$1+QUOTIENT((SPALTE()-17);7)=(KALENDERWOCHE(HEUTE();2)-1));"Ja";"Nein")

Der 2. $ bei $Q$1 ist der Tatsache geschuldet, dass dieser Test in Zeile 9 läuft. Jedenfalls wird korrekt im besagten Raster jede Spalte der aktuellen Woche 24 mit einem Ja dekoriert - der Rest mit Nein.

Kannst du das kürzer - dynamisch, sodass das Kopiern der 7 Wochenspalten alles ohne Handarbeit bereithält? Die Funktionalität mit wenigen Handgriffen statt meiner Makros eine neuen Monat einzurichten muss erhalten bleiben, deswegen auch all diese Beschränkungen…

Was nun eben nicht arbeitet ist die aufgezeigte Logik im WENN in einer Bedingten Formatierung da sich diese mit der o.g. Ausrede komplett querlegt.
In Testbereichen anderenorts ist mir das Färben leerer Zellen (mit XL gefälligen Bedingungsformulierungen) gelungen.

Nun also steht die Frage im Raum warum die Bedingung abgewiesen wird - und weniger warum nicht geschaltet wird.

Eric March

Answer 7

Hallo Eric,

die Win98SE-VM mit Office 2000 hab ich Letztens gelöscht und die Tabellen deshalb unter Excel 2007 im Kompatibilitätsmodus 97-2003 erstellt. Aber selbst unter Excel 2003 in einer XP-VM funktioniert diese Formatierung nicht. Lösch ich sie und leg sie neu unter Excel 2003 an, kommt die gleiche Meldung bzgl. der Bezüge auf andere Tabellen, obwohl das eigentlich Quatsch ist. Es sei denn, die Ausführung der Analyse-Addins für 'Kalenderwoche' wird als außerhalb der Tabelle befindlich angesehen.

Unter LibreOffice Calc gibt es mit den Bedingten Formatierungen in Tabelle Juni 2012 (3) übrigens keine Probleme. Nur mal so für den Fall, dass sich diese Firma kein aktuelleres MS Office leisten kann oder will.

Gruß
Kalle

Answer 8

Es sei denn, die Ausführung der Analyse-Addins für 'Kalenderwoche' wird als außerhalb der Tabelle befindlich angesehen.

Mit ein paar Kilo Zähnekrischen: das wäre die einzig brauchbare Erklärung - und dann kann ich die Komforverbesserung knicken.

Weder meinen Chef noch andere werde ich überreden können auf LO/OOo umzusatteln (auch, weil da die überwältigende Mehrheit wichtiger Makros nicht liefe).
Davon ab; ist gar nicht so lange eher, da wurde noch O97 benutzt, bis auf ein paar Spezis die 2k verwendeten. Auch da muss es eine Makro-Inkompatibilität geben, und da man ja nieeeemals zu O97 zurück wollte muss ich mich nun mit O2k plagen was gegenüber O97 keineswegs nur Vorteile bietet…

Sieht also so aus da knicken zu können… Aber so schnell gebe ich nicht auf. Irgendwie komme ich noch an meine KW heran!

Eric March