Bed. Formate ändert Bezug selbständig

Forumthread: Bed. Formate ändert Bezug selbständig

18.02.2016 11:58:23

Martina

Hallo Forum,
eine ähnliche Frage gabs schon mal, nur keine Lösung:
habe eine Tabelle mit Filtern (die auch ab und zu sortiert wird, je nach Bedarf).
Wende auf Spalte $D:$D eine Formel an, die Fällige Datensätze nach einem Kriterium rot hervorhebt. Geht alles super!
ABER:
ich gebe die Tabelle per Email ans Büro weiter und die arbeiten damit. Nach einigen Tagen sehe ich mir die Datei an:
ERGEBNIS: der Bereich =$D:$D auf den die Formel angewendet wird, ist total zerschossen (Formel selbst ist weiter richtig).
Da steht dann z.B. =$D20:$D25;$D170:§$D172 usw.
Im Büro werden Datensätze gefiltert und neue DS unten angehängt.
Warum zerschießt der Bereich von allein?
Diese Formatierung ist sehr wichtig und bestimmt die zukünftige Einsatzplanung. Nun kann ich mich nicht auf das Ergebnis verlassen!
(Beide, Büro und ich benutzen Excel 2010)
Ist euch das auch schon mal passiert?
Danke für Eure Hilfe im Voraus!!
Martina

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Bed. Formate ändert Bezug selbständig

18.02.2016 12:09:57

Daniel

Hi
ja, ist ein grosses Problem mit der neuen Bedingten Formatierung.
vorallem beim Zeilenlöschen oder Einfügen wir der Bereich geteilt und mehrfach in den Einzelnen Teilbereichen wieder angelegt.
die einzige mir bekannte Abhilfe wäre eine bestimmte Arbeitsmethode, nämlich folgende:
a)Zeilen löschen:
- kopieren aller Zeilen unterhalb der zu löschenden Zeile inclusive der ersten Leerzeile unterhalb der Daten.
- kopierte Daten eine Zeile höher einfügen und die zu löschende Zeile überschreiben
b) Zeilen einfügen:
- kopieren alle Zeilen unterhalb der Stelle, ab der eingefügt werden soll
- kopierte Daten eine Zeile tiefer einfügen
- die so entstandene doppelte Zeile mit den neuen Werten überschreiben.
oder man muss sich halt die Zeit nehmen, nach der Überarbeitung der Tabelle die Bedingten Formate neu einzurichten.
Gruß Daniel

AW: Bed. Formate ändert Bezug selbständig

18.02.2016 12:26:58

Martina

Hallo Daniel,
danke für deine schnelle Antwort, die sehr Hilfreich ist!!!
Wenn ich dich richtig verstehe, könnte man das Löschen von Datensätzen auch verbieten, wäre zumindest bei mir eine sinnvolle Lösung.
Du schreibst "vor allem...", d.h. es könnte noch weitere Bearbeitungsschritte geben, wo sich die Bed. Formatierung zerschießt (außer einfügen und löschen)?
Einfügen macht bei meiner Excel-Datei nur Sinn, wenn immer nur unten der nächste DS eingefügt wird. Dann sollte die Formatierung wohl funkt.?
Ich forsche mal, was die im Büro mit der Tabelle machen...und melde mich nochmal.
Vielleicht weiß jemand ob das bei MS bekannt ist?
Gruß Martina

AW: dies Problem ist etwas komplexer ...

18.02.2016 13:15:07

...

Hallo Martina,
... als bisher dargestellt.
Die vorhandenen Bereichsdefinitionen sind abhängig von den Zell-Aktionen die vom Nutzer mit und innerhalb diesen Bereiches vornehmen. Aber auch von der Art der evtl. definierten Formeln der bedingten Formatierung und dies alles im allseitigen Zusammenwirken!
Bei bed. Formeldefinitionen können sich sich vor allem Adressen(absolut oder relativ) -bezogene Formeldefinitionen oft problematisch auswirken. Dies vor allem dann, wenn Zellen und oder ganze Zellbereiche bzw. Zeilen/Spalten mit solchen bedingten Formatierungen innerhalb des Definitionsbereiches kopiert und wo anders eingefügt oder nur verschoben werden.
Dies gilt auch schon, wenn keine bed. Formeln definiert sind, also nur einfache bedingte Definitionen vorgenommen wurden. Im letzteren Fall ist jedoch das Löschen von Zeilen und oder Spalten (nicht aber von einzelnen Zellen) unproblematisch, denn das wird von Excel noch korrekt abgefangen. Das Problem mit dem Kopieren und oder Verschieben bleibt aber auch da.
MS kennt das Problem sicherlich. Aber ich kann mir auch vorstellen, dass eine korrekte Berücksichtigung aller Eventualitäten ziemlich schwierig abzufangen ist.
Zwischenzeitlich kann man sich standardmäßig nur mit Restriktionen etwas helfen:
Nie derartige Zellen/Zellbereiche kopieren und in derartige Bereiche einfügen und auch solche Zellen/Zellbereiche nicht verschieben. Ersatz dafür: Immer nur die Werte kopieren und an entsprechende Zielstellen einfügen.
Zu der angesprochenen bed. Formelproblematik: Diese wirkt sich auch beim Einfügen von Zeilen oder Löschen entsprechend negativ aus. Abhilfe: keine Adressen bezogenen Formeln einsetzen. Ersatz: absolut oder relativ stets über INDEX() definieren. Aber das ist ein Kapitel für sich.
Gruß Werner
.. , - ...

AW: dies Problem ist etwas komplexer ...

19.02.2016 08:28:25

Martina

Hallo Werner,
boah (schreibt man das so?)!!
Das ist ja Wahnsinn. Naja, was mir evtl. bleibt ist: wenn so eine Abfrage nach fälligen Daten erfolgen soll, könnte ich vielleicht eine Prozedur erstellen, die vorher jedes Mal die Bezüge neu setzt (und das dann auf einen "Knopf" legen, der vorher ausgeführt werden muß).
Im Grunde mache ich das ja nun immer "mit der Hand"..
Könntest du mir das o.k. geben, dass so eine Vorgehensweise eine Lösung ist?
Wenn ja: würde ich allerdings Eure Hilfe brauchen, denn das bekomme ich (leider) nicht allein hin. Ich würde dann einen neuen Thread aufmachen....
Danke nochmal!!!
Martina

AW: ich setzt den thread offen ...

19.02.2016 08:43:05

...

Hallo Martina,
... denn aus VBA-Lösungen halte ich mich grundsätzlichen heraus. Aber ein neuer thread ist deswegen mE nicht notwendig. Außerdem wäre hier für potentielle Helfer gleich der Hintergrund Deines Anliegens nachzulesen. Doch dieses allein wird nicht ausreichen, um eine für Dich befriedigende Lösung zu erstellen. Da bedarf es wohl zusätzlicher Infos Deinerseits zu Deiner Arbeitsmappe (Datenstruktur etc.)
Gruß Werner
.. , - ...

AW: ich setzt den thread offen ...

19.02.2016 09:01:33

Martina

o.k., dann bereite ich das Beispiel mal vor und melde mich anschließend!
Vielen Dank, Martina

Infobox / Tutorial

Bedingte Formatierung in Excel: Probleme und Lösungen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Bedingte Formatierung einrichten:
- Markiere den Bereich, auf den die bedingte Formatierung angewendet werden soll (z.B. Spalte D).
- Gehe zu „Start“ > „Bedingte Formatierung“ > „Neue Regel“.
- Wähle „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.
- Gib die gewünschte Formel ein (z.B. =D1<HEUTE() für fällige Datensätze).
- Stelle das Format (z.B. rot) ein und klicke auf „OK“.
Bereich überprüfen:
- Achte darauf, dass der Bereich immer korrekt eingestellt ist (z.B. $D:$D für die gesamte Spalte).
Nach Änderungen neu überprüfen:
- Nach dem Einfügen oder Löschen von Zeilen, überprüfe die bedingte Formatierung, um sicherzustellen, dass sie nicht zerschossen ist.

Häufige Fehler und Lösungen

Problem: Die bedingte Formatierung ändert sich selbstständig:
- Lösung: Vermeide das Kopieren und Einfügen von Zellen innerhalb des formatierten Bereichs. Nutze stattdessen „Werte einfügen“.
Problem: Bereichsdefinition wird beim Löschen oder Einfügen von Zeilen zerschossen:
- Lösung: Verwende die beschriebenen Methoden von Daniel, um Zeilen sicher zu löschen und einzufügen. Dies kann helfen, die korrekte Formatierung beizubehalten.
Problem: Relativer Bezug verursacht Fehler:
- Lösung: Nutze absolute Bezüge (z.B. $D$1) oder INDEX()-Formeln, um sicherzustellen, dass die Formatierung nicht durch Zellverschiebungen beeinträchtigt wird.

Alternative Methoden

VBA-Lösungen: Wenn du regelmäßig Probleme mit der bedingten Formatierung hast, könnte eine VBA-Prozedur hilfreich sein. Diese kann automatisch die Formatierung anpassen, wenn Daten geändert werden. Ein einfaches Skript könnte so aussehen:
```
Sub UpdateConditionalFormatting()
  Dim ws As Worksheet
  Set ws = ThisWorkbook.Sheets("DeinBlattname")
  ws.Range("$D:$D").FormatConditions.Refresh
End Sub
```
Manuelles Zurücksetzen: Nach jeder größeren Datenänderung kannst du die bedingte Formatierung manuell zurücksetzen und neu anwenden, um sicherzustellen, dass sie korrekt funktioniert.

Praktische Beispiele

Fällige Datensätze rot markieren: Verwende die Formel =D1<HEUTE() für die bedingte Formatierung, um automatisch alle fälligen Datensätze in Spalte D rot hervorzuheben.
Dynamische Bereiche: Wenn du regelmäßig neue Daten hinzufügst, könnte es sinnvoll sein, die bedingte Formatierung auf eine Tabelle anzuwenden. Tabellen in Excel passen sich dynamisch an neue Daten an und helfen, das Problem der sich selbstständig ändernden Formatierung zu minimieren.

Tipps für Profis

Vermeide komplexe Formeln in der bedingten Formatierung: Halte die Formeln einfach, um die Wahrscheinlichkeit von Fehlern zu reduzieren.
Regelmäßige Überprüfung: Kontrolliere regelmäßig die angewendeten bedingten Formatierungen, insbesondere nach größeren Änderungen an den Daten.
Dokumentation: Halte eine kurze Dokumentation darüber, welche bedingten Formatierungen angewendet werden und welche Formeln verwendet werden. So kannst du schnell nachvollziehen, was bei Problemen schiefgelaufen sein könnte.

FAQ: Häufige Fragen

1. Warum ändert sich die bedingte Formatierung selbstständig? Die bedingte Formatierung kann sich ändern, wenn Zellen innerhalb des definierten Bereichs kopiert, eingefügt oder verschoben werden. Vermeide solche Aktionen oder nutze die empfohlenen Methoden zur Zeilenbearbeitung.

2. Wie kann ich sicherstellen, dass die bedingte Formatierung immer korrekt bleibt? Nutze absolute Bezüge oder INDEX()-Formeln in deiner bedingten Formatierung. Überprüfe nach Änderungen regelmäßig, ob die Formatierung noch korrekt angewendet wird.

Beliebteste Forumthreads (12 Monate)

Bed. Formate ändert Bezug selbständig

Forumthread: Bed. Formate ändert Bezug selbständig

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Bedingte Formatierung in Excel: Probleme und Lösungen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread