Verschieben von Zellinhalten - Fehler Bezug

[gabriela]

Liebe Community!

Ich nutze eine umfangreiche Exceltabelle für Planungszwecke.
 
Die Spalten C bis L enthalten Daten, welche manuell eingegeben werden können, welche stehts in einer bestimmten Abfolge stehen müssen.
In den Spalten A bis B und M bis Q werden aus diesen Daten neue Werte errechnet. Diese Zellen sind gegen manuelle Eingaben geschützt.
   
Problem: Es kann nun vorkommen, dass eine Eingabe eingefügt werden soll. Dazu muss der gesamte Bereich der Spalten C bis L ab dieser Zeile ausgeschnitten und eine Zeile tiefer wieder eingefügt werden, so dass die neuen Daten in die nun freie Zeile eingefügt werden können. 

Das funktioniert aber nicht. Die Formeln dieser Art "=WENN(ODER(D9="S";D9="ss";D9="c");SUMME(R8:W8);0)" finden ihren Bezug nicht mehr ("=WENN(ODER(#BEZUG!="S";#BEZUG!="ss";#BEZUG!="c");SUMME(R9:W9);0)")
   

Wenn ich den Zellenbezug fest mit "$" binde, ändert das nichts.

Wie kann ich erreichen, dass die berechneten Felder von diesen Änderungen nicht berührt werden (also weiterhin auf die Daten schauen, für die sie geschrieben wurden)?

Vielen Dank und LG
Gabriela

[lupo1]

Das geschieht über Beschreibung statt Adressierung:

=WENN(ODER(D9="S";D9="ss";D9="c");SUMME(R8:W8);0)

könnte man z.B. als

=ODER(INDEX($D:$D;9)={"S";"ss";"c"})*SUMME(INDEX($R:$W;8;))

umschreiben. Sollen auch die Spalten derart betoniert werden, dann:

=ODER(INDEX($A:$XFC;9;4)={"S";"ss";"c"})*SUMME(INDEX($A:$XFC;8;18):INDEX($A:$XFC;8;23))

oder als

=ODER(INDIREKT("D9")={"S";"ss";"c"})*SUMME(INDIREKT("W8:R8"))

Problematisch ist es dann, wiederum entsprechend weiterzukopieren, so dass sich wenigstens beim Kopieren die Bezüge entsprechend anpassen, ohne dass dies beim Löschen/Einfügen der Bezüge geschieht. Das ginge aber mit benannten Formeln.

[Rabe]

Hi,

wenn die Daten ausgeschnitten und eine Zeile tiefer eingefügt werden, stehen ja in der dann leeren Zeile keine Formeln mehr drin.
Dann kann auch einfach durch einfügen eienr neuen Zeile eine Leerzeile an der entsprechenden Stelle eingefügt werden.

Entweder mit Zeile markieren und STRG-+ oder Rechtsklick, "Zellen einfügen".

[gabriela]

könnte man z.B. als

=ODER(INDEX($D:$D;9)={"S";"ss";"c"})*SUMME(INDEX($R:$W;8;))

Danke - Problem ist gelöst.
=WENN(INDEX($D:$D;20)="KT";0,5;0)

Ich habe zum Umschreiben der Formeln eine Makroschleife verwendet, weil ja ein einfaches Befüllen mit Anpassung der Bezüge nun nicht mehr geht. Hat den Vorteil, dass Änderungen im Makro einfacher zu schreiben sind.

For n = 2 To 1250
'Spalte W: 0,5 Stunden, wenn D ="KT" (Case external)
Cells(n, 23).Formula = "=WENN(index($D:$D;" & n & ")=""KT"";0,5;0)"

usw. etc.

@Ralf: In den zu verschiebenden Zellen stehen ja keine Formeln. Ich habe den Begriff "Zeile" unscharf verwendet. Präziser "der Bereich C bis L einer Zeile". Die Zellen mit Formeln stehen rechts davon und dürfen nicht mit verschoben werden.

LG Gabriela

[Rabe]

Hi Gabriela,

@Ralf: In den zu verschiebenden Zellen stehen ja keine Formeln. Ich habe den Begriff "Zeile" unscharf verwendet. Präziser "der Bereich C bis L einer Zeile". Die Zellen mit Formeln stehen rechts davon und dürfen nicht mit verschoben werden.

sowas dachte ich mir schon.

Sind die Formeln jeweils über die komplette Spalte die gleichen?
Dann könntest Du aus Deiner Datensatzliste eine intelligente Tabelle formatieren: STRG-L mit Überschriften.

Wenn Du dann eine Zeile ein- oder anfügst, dann werden diese Formeln automatisch mitgeführt und weitergeschrieben und Du hast den neuen Eingabebereich leer! Das ist viel einfacher als solche Formelkonstrukte.

  intelligente Tabelle.xlsb (Größe: 8,71 KB / Downloads: 3)

[gabriela]

Danke für den weiteren Lösungsvorschlag.

Da müsste ich mich erst mal intensiv reinarbeiten. Vielleicht, wenn ich mal wieder so ein Problem zu lösen habe. Aber die obige Lösung ist ja perfekt und tut exakt das, was nötig war. Das Umschreiben der Formeln und Anlegen des Makros ging sehr schnell.

LG Gabriela

[Rabe]

Hi,

für den Lösungsweg mit der intelligenten Tabelle sind eine Tastenkombination und ein Klick notwendig, das ist in 2 Sekunden erledigt.

Und die Datei wird nicht durch Formeln und Makro aufgebläht und auf lange Sicht langsam. Die Liste ist dann automatisch auch nicht größer, als in diesem Moment erforderlich.

[gabriela]

Lieber Ralf!

Ich glaube es dir ja (nur nicht, dass eine höchstintelligente Tabelle in 2 Sekunden meine Formelwerke über die vielen Spalten kennen kann, welche ich über Wochen erarbeitet habe (außerhalb und unabhängig von Excel Beziehungen und logische Verknüpfungen definiert; hier ja nur in Excel umgesetzt).

Wie geschrieben: Bin glücklich, dass es funktioniert und mache mich an die nächste Aufgabe (die vorerst nichts mit Excel zu tun hat).

Das Makro wird ja doch nur ein einziges Mal ausgeführt (und wenn Beziehungen/Berechnungen geändert werden müssen) und belastet die flüssige Arbeitsweise in keinster Weise. Bei den jetzt ca. 1000 Datensätzen ist bei Eingaben keine Reaktionszeit festzustellen; die Verschiebungen, um die es ging, sind seltene Ausnahmen (wenn jemand gepennt hat). Hab's gerade mal probiert: Ein Wimpernschlag.

LG Gabriela

[WillWissen]

Hi Gabriela,

Ich glaube es dir ja (nur nicht, dass eine höchstintelligente Tabelle in 2 Sekunden meine Formelwerke über die vielen Spalten kennen kann, welche ich über Wochen erarbeitet habe (außerhalb und unabhängig von Excel Beziehungen und logische Verknüpfungen definiert; hier ja nur in Excel umgesetzt).

wie wär's eigentlich, wenn du ein klein wenig Zeit investierst und dir von Tante Gugl etwas über intelligente Tabellen berichten lässt? Dann fällt dir nämlich auch der "Zeitglauben" ein wenig leichter. :32:

[Rabe]

Hi Gabriela,

(nur nicht, dass eine höchstintelligente Tabelle in 2 Sekunden meine Formelwerke über die vielen Spalten kennen kann, welche ich über Wochen erarbeitet habe

Das Makro wird ja doch nur ein einziges Mal ausgeführt (und wenn Beziehungen/Berechnungen geändert werden müssen) und belastet die flüssige Arbeitsweise in keinster Weise. Bei den jetzt ca. 1000 Datensätzen ist bei Eingaben keine Reaktionszeit festzustellen; die Verschiebungen, um die es ging, sind seltene Ausnahmen (wenn jemand gepennt hat). Hab's gerade mal probiert: Ein Wimpernschlag.

1.) die Tabelle muß sie ja nicht "kennen", sie übernimmt die Formeln einfach.
2.) wenn es nur einmalig und für seltene Ausnahmen benötigt wird, dann reicht vermutlich das Formel-Makro-Konstrukt.

Aber:
zum Lernen ist die intelligente Tabelle durch Ausprobieren und Vergleich mit der nun bestehenden gut geeignet.