Formel funktioniert nicht (WENN)

[StefanM]

Guten Tag,

ich möchte eine ganze Zeile in ein neues Arbeitsblatt kopieren, wenn eine bestimmte Spalte Zahlen enthält.

Bei einer bestimmten Zahl funktioniert diese Formel:
=WENN(ZEILE(Tabelle1!1:1)>ZÄHLENWENN(Tabelle1!$A$2:AD$2606;4450816207);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$I$2:$I$2606=4450816207;ZEILE(Tabelle1!$2:$2606));ZEILE(A1))))

Es soll jedoch der Bereich 4000000000 bis 4999999999 abgedeckt werden, bzw. alle Zellen berücksichtigt werden welche Zahlen enthalten.

Gebe ich diese Formel ein, um alle Zahlen bis 4999999999 auszugeben, erhalte ich kein Ergebnis:
=WENN(ZEILE(Tabelle1!1:1)>ZÄHLENWENN(Tabelle1!$A$2:AD$2606;"<="&4999999999);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$I$2:$I$2606<=4999999999;ZEILE(Tabelle1!$2:$2606));ZEILE(A1))))

Umgekehrt, alle Zahlen größer wie 1 funktioniert hingegen, nur werden dann auch die Zeilen mit Überschriften ausgegeben und das darf nicht sein:
=WENN(ZEILE(Tabelle1!1:1)>ZÄHLENWENN(Tabelle1!$A$2:AD$2606;">="&1);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$I$2:$I$2606>=1;ZEILE(Tabelle1!$2:$2606));ZEILE(A1))))

Wichtig, es sollen nur Zellen berücksichtigt werden welche Zahlen enthalten. Nicht die Zellen mit Überschriften.

Weiss hierzu jemand Rat? Vielen Dank.

[Peter]

Hallo,

ich habe momentan kein XL zur Verfügung, deshalb nur als Idee:

1. Arbeite mit einer Hilfsspalte und frage dort ab ob der Wert innerhalb deines Bereiches liegt und trage bei Wahr z.B. ein X ein. Verwende dann diese Hilfsspalte in deiner eigentlichen Formel.

2. Ersetze Zählenwenn in der Formel durch Summenprodukt, dort kannst du auch eine Bereich von...bis prüfen

3. Nutze alternative Möglichkeiten wie z.B. PowerQuery. Das ist anfangs zwar etwas Einarbeitung das wird sich aber auf lange Sicht vermutlich auszahlen.

Alle Ideen natürlich abhängig vom Aufbau deiner Tabelle die genauso im Nebel verborgen ist wie der momentane Blick aus dem Fenster

[shift-del]

Hallo

Code:

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$2:$2606)/(Tabelle1!$I$2:$I$2606<=4999999999)/(Tabelle1!$I$2:$I$2606>=4000000000);ZEILE(A1)));"")

[StefanM]

Super. Vielen Dank. :)

Nun habe ich noch eine Frage zur Ausgabe:

Ich habe jetzt. z.B. 300 Zeilen im Ziel-Sheet mit dieser Formel hinterlegt. Hier auch ein gesonderter Rahmen.
Seitenansicht gibt mir alle 300 Zeilen zum Drucken aus. Ich möchte aber nur die tatsächlich übertragenen Drucken, inkl. Rahmen.

[BoskoBiati]

Hallo,

da ich kein Fan von großen Zahlen in Formeln bin, würde ich das so schreiben:

Code:

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$2:$2606)/(Tabelle1!$I$2:$I$2606<5*10^9)/(Tabelle1!$I$2:$I$2606>=4*10^9);ZEILE(A1)));"")

[StefanM]

Hallo,

da ich kein Fan von großen Zahlen in Formeln bin, würde ich das so schreiben:

Code:

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$2:$2606)/(Tabelle1!$I$2:$I$2606<5*10^9)/(Tabelle1!$I$2:$I$2606>=4*10^9);ZEILE(A1)));"")

Vielen Dank.

Was hälst du generell von dieser Formel bzw. gibt es noch eine Bessere?
Es soll anhand einer bestimmten Zahl in einer Zelle die ganze Zeile in ein neues Arbeitsblatt kopieren:

=WENN(ZEILE(Tabelle1!1:1)>ZÄHLENWENN(Tabelle1!$A$2:AD$2176;1);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$A$2:$A$2176=1;ZEILE(Tabelle1!$2:$2176));ZEILE(A1))))

Die Formel funktioniert so weit, Fehlermeldungen "Bezug" habe ich mit weisser Schrift ausgeblendet.
Allerdings schleichen sich immer wieder Zeilen mit Bezugsfehlern in das Ziel-Blatt, welche ich jedes Mal händisch löschen muss. Beispiel:
=WENN(ZEILE(Tabelle1!#BEZUG!)>ZÄHLENWENN(Tabelle1!$A$2:AD$2176;1);"";INDEX(Tabelle1!A:A;KKLEINSTE(WENN(Tabelle1!$A$2:$A$2176=1;ZEILE(Tabelle1!$2:$2176));ZEILE(A3))))
Wieso passiert dies?

[BoskoBiati]

Hallo,

das würde ich mit VBA lösen, da in der Ursprungstabelle offensichtlich regelmäßig Zeilen gelöscht werden, die dann zu einem Bezugsfehler führen. Evtl hilft es auch nur so zu schreiben:

WENN(ZEILE(1:1)>

[StefanM]

Hallo,

das würde ich mit VBA lösen, da in der Ursprungstabelle offensichtlich regelmäßig Zeilen gelöscht werden, die dann zu einem Bezugsfehler führen. Evtl hilft es auch nur so zu schreiben:

WENN(ZEILE(1:1)>

Mit dieser Formel funktioniert es auch:

Code:

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$2:$2606)/(Tabelle1!$A$2:$A$2606=1)/(Tabelle1!$A$2:$A$2606>=1);ZEILE(A1)));"")

Doch leider wird die Eingabe bzw. Berechnung nach jeder Eingabe nun endlos langsam (19 verschiedene Zielblätter).
Wie kann man das beschleunigen?

[BoskoBiati]

Hallo,

klar, dass die funktioniert, da gibt es den Bezug auf Tabelle1!1:1 nicht! Verstanden hast Du die Formel allerdings nicht, denn sie ist nicht sehr sinnvoll:

Code:

=WENNFEHLER(INDEX(Tabelle1!A:A;AGGREGAT(15;6;ZEILE(Tabelle1!$2:$2606)/(Tabelle1!$A$2:$A$2606>=1);ZEILE(A1)));"")

Auch wenn es nicht als Matrixformel ausgewiesen ist, die Formel macht nichts anderes als eine Matrixformel und könnte durchaus etwas Rechenzeit beanspruchen, wobei Deine Ursprungsformel wohl mehr Zeit brauchen sollte!

[StefanM]

Hallo,

das würde ich mit VBA lösen, da in der Ursprungstabelle offensichtlich regelmäßig Zeilen gelöscht werden, die dann zu einem Bezugsfehler führen. Evtl hilft es auch nur so zu schreiben:

WENN(ZEILE(1:1)>

Wie würde diese Formel in meiner ausgeschrieben lauten
 Bin hier nur Laie.  Vielen Dank:)