Zeilentausch ändert Formeln

[Maus]

Hallo,

Ich habe grundlegend folgendes Problem. Über ein simples "Makro aufzeichnen" füge ich eine Zeile einer Tabelle hinzu. Damit sich die Formeln für die Tabelle (bedingte Formatierung, etc.) nicht ändern, habe ich die neue Zeile nicht an Stelle "Zeile 2" eingefügt sondern als "Zeile 3". Dies lässt den Bereich A2:A100 ( A2:A101 --> wegen der neuen Zeile ) unverändert. Wenn man eine neue Zeile einer Tabelle als erste Zeile ( Zeile 2 ) einfügt, ändert sich der Bereich für vorherige Formeln und bedingte Formatierungen auf A3:A101.

Soweit so gut !! :18:

Wenn ich im Nachhinein jedoch die Zeile dann tausche ( Beispiel: 3 und 2 ) , dann ändert sich im Nachhinein die Formeln und bedingte Formatierung auf A3:A101. Da es für die Übersicht der Tabelle aber schöner ist, wenn die neue Zeile immer zu Beginn der Tabelle kommt ( Zeile 2 ), gibt es dafür eine Lösung??

Liebe Grüße

Die Maus :)

[WillWissen]

Hi,

generell würde ich die Tabelle als "intelligente Tabelle" formatieren; dann musst du dazwischen keine Zeilen einfügen. Die Tabelle erweitert sich mit automatischer Übernahme bestehender Formeln.

[Maus]

Kurz zur Erklärung:

Tabelle 1: "Prüftabelle"enthält Daten die gepflegt werden, es werden also immer mal wieder Zeilen hinzugefügt. Immer am Anfang der Tabelle ( Zeile 2 ). Leider zur Zeit immer Zeile 3, da sonst sich Formeln verändern.

Die Tabelle enthält eine bedingte Formatierung mit der Formel =$C2=1 und wird gelb wenn diese erfüllt ist, für den Bereich A2:B399.

Tabelle 2: Enthält ein Teil der Daten aus Tabelle 1. Wenn eine gewisse Bedingung erfüllt ist ( Tabelle 1 : Spalte C = 1 ), dann wird automatisch die Daten aus Spalte A und B für diese Zeile in Tabelle 2 übernommen.

Folgende Formel für die Spalte A:

=WENN((WENNFEHLER(INDEX(Prüftabelle!A$2:A$399;AGGREGAT(15;6;ZEILE(Prüftabelle!$C$2:$C$399)-1/(Prüftabelle!$C$2:$C$399=1);ZEILE(A1)));"")=0);"";(WENNFEHLER(INDEX(Prüftabelle!A$2:A$399;AGGREGAT(15;6;ZEILE(Prüftabelle!$C$2:$C$399)-1/(Prüftabelle!$C$2:$C$399=1);ZEILE(A1)));"")))


Problem:

Wenn ich eine neue Zeile ( als erste Zeile , also Zeile 2 ) in Tabelle 1 "Prüftabelle" einfüge, ändert sich die bedingte Formatierung zu =$C3=1 mit A3:B400.

Die Formel in Tabelle 2 ändert sich zu:

=WENN((WENNFEHLER(INDEX(Prüftabelle!A$3:A$400;AGGREGAT(15;6;ZEILE(Prüftabelle!$C$3:$C$400)-1/(Prüftabelle!$C$3:$C$400=1);ZEILE(A1)));"")=0);"";(WENNFEHLER(INDEX(Prüftabelle!A$3:A$400;AGGREGAT(15;6;ZEILE(Prüftabelle!$C$3:$C$400)-1/(Prüftabelle!$C$3:$C$400=1);ZEILE(A1)));"")))

Lösung:

Die neue Zeile wird als Zeile 3 hinzugefügt, somit ändert sich der Start des Bereichs A2: ... nicht ...

Idee:

Dann dachte ich, ich könnte einfach die beiden ersten Zeilen vertauschen und Excel so austricksen. Falsch gedacht! Dann ändert sich der Bereich der Formel in Tabelle 2 wieder. :22:


Gibt es eine Lösung? Bin gespannt auf eure Ideen :100:

[WillWissen]

Hi,

hast du meinen Tipp (an einer Kopie) ausprobiert?

[RPP63]

Moin!
Ein Tipp:
Wenn bedingte Formatierungen vorhanden sind, sollte man NIEMALS Zeilen einfügen!
Begründung:
Dies fragmentiert die Bereiche, die dann, um das Sheet nicht irgendwann auszubremsen, mühsam manuell zusammengefügt werden müssen.
(die Probleme mit Formeln siehst Du ja selber)
Besser ist in jedem Fall: Anhängen an intelligente Tabelle (siehe Günter).

Gruß Ralf

[Maus]

Hi,

hast du meinen Tipp (an einer Kopie) ausprobiert?

Hallo, die Tabelle ist meiner Meinung nach schon "intelligent".

Gibt es nicht die Möglichkeit die Formel intelligent zu machen, also den Bereich A2:B399 auf komplette Zeile A:B oder ähnliches zu ändern? Dann würde es ja keine "festen Bereiche" geben oder?

[Maus]

Moin!
Ein Tipp:
Wenn bedingte Formatierungen vorhanden sind, sollte man NIEMALS Zeilen einfügen!
Begründung:
Dies fragmentiert die Bereiche, die dann, um das Sheet nicht irgendwann auszubremsen, mühsam manuell zusammengefügt werden müssen.
(die Probleme mit Formeln siehst Du ja selber)
Besser ist in jedem Fall: Anhängen an intelligente Tabelle (siehe Günter).

Gruß Ralf

Hallo Ralf,

Danke auch für deine Antwort.

Mein Problem:

Bin super schlecht in Excel und lerne es gerade anhand meines Problems. Ich habe auf dem ersten Arbeitsblatt sowas wie ein Formular, in dem der Nutzer Name, Adresse, etc. eingibt. Über ein Button mit Makro dahinter ( über Makro aufzeichnen ) kopiert er über Strg + C und Strg + V die Daten als neue Zeile auf das zweite Arbeitsblatt, also in die Prüftabelle. Deswegen wüsste ich nicht, wie ich die Zeile hinten ranhängen könnte :) Würde es dann aber nicht auch nicht beachtet werden bei der bedingten Formatierung und der Formel.

PS. die bedingte Formatierung bleibt unverändert, wenn ich eine Zeile ( An der Stelle Zeile 3 ) einfüge.  oder nicht? ( bei mir laut Excel schon   )

Hoffe du verstehst mein Problem?

[WillWissen]

Hi,

ich würde die Daten nicht über ein Formular sondern direkt in das Datenblatt schreiben. In einer als Tabelle formatierten Tabelle fügen sich die Zeilen unten automatisch an, die Formeln werden fortgesetzt und auch die bF erweitert sich.

[Maus]

Hi,

ich würde die Daten nicht über ein Formular sondern direkt in das Datenblatt schreiben. In einer als Tabelle formatierten Tabelle fügen sich die Zeilen unten automatisch an, die Formeln werden fortgesetzt und auch die bF erweitert sich.

Wenn ich diese Tabelle selber pflegen würde, würde ich es auch anders machen. Für die Bediener/innen ist es einfacher mit meinem "Formular" , also ein Deckblatt wo man in die Zeilen B2 , B4 , B6 ; ... die Daten eingetragen werden und in der Spalte davor die dazugehörige Erklärung : Name , Vorname, Datum , ....


Habe die Datei komplett fertig, auch mit Wenn Bedienung für die Ausfüllung von PLZ zu Ort ( Wenn PLZ = XXXXX dann ORt = XXXX , sonst WENN ORt = XXXX usw. ) in der Tabelle 1 und drop down listen. Der einzige Fehler ist halt die Änderung der "doofen" Formel in Tabelle 2 :22: :22: :22: :22:

Gibt es eine vielleicht einfache nicht perfekte schöne Lösung?


Danke für deine/ eure Mühen :)

[Rabe]

Hi,

Habe die Datei komplett fertig, auch mit Wenn Bedienung für die Ausfüllung von PLZ zu Ort ( Wenn PLZ = XXXXX dann ORt = XXXX , sonst WENN ORt = XXXX usw. ) in der Tabelle 1 und drop down listen.

das ist mit SVERWEIS() und einer kleinen Hilfstabelle einfacher gelöst, als mit einer verschachtelten WENN-Formel.