Formel-Anpassung

Question

Hallo,

ich habe in einem Excel-Sheet eine Formel, die u.a. folgende Zellzuweisung beinhaltet:

....ZEILE($1:$400)...
Gesamte Formel:
=VERKETTEN(WENN(ZEILEN(59:60)>ZÄHLENWENN(Veränderungen!$A:$A;$B59);"";INDEX(Veränderungen!$L:$L;KKLEINSTE(WENN(Veränderungen!$A$1:$A$400=$B59;ZEILE($1:$400));ZEILE(A$2))));" / ";WENN(ZEILEN(59:60)>ZÄHLENWENN(Veränderungen!$A:$A;$B59);"";INDEX(Veränderungen!$H:$H;KKLEINSTE(WENN(Veränderungen!$A$1:$A$400=$B59;ZEILE($1:$400));ZEILE(A$2))));" / ";WENN(ZEILEN(59:60)>ZÄHLENWENN(Veränderungen!$A:$A;$B59);"";INDEX(Veränderungen!$I:$I;KKLEINSTE(WENN(Veränderungen!$A$1:$A$400=$B59;ZEILE($1:$400));ZEILE(A$2)))))

Obwohl ich mit "$" verhindern möchte, dass sich die Formel verändert passiert dies wenn ich in der entsprechenden Tabelle Zeilen einfüge: aus zeile($1:$400) wird dann zeile($1:$402), wodurch die Formel dann einen Fehler erzeugt. Hat jemand einen Tipp wie ich das umgehen kann?
Danke und Gruß!
Andreas

Answer 1

Hallo Andreas,

da hilft die Tabellenfunktion INDIREKT()

Wenn Du nun fragst wo und wie Du die einsetzen sollst, antworte ich: "Stelle eine Beispielmappe ein, denn ich müsste in diesem Falle auch erst testen"!

Gruß
Rainer

Answer 2

Danke Rainer für den Tipp. Ich probiere es mal im Kleinen. Die Datei ist über mehrere Arbeitsblätter verbunden, so dass es schon schwierig wird eine Beispielmappe zu produzieren, die dann auch wieder zum eigentlichen Problem zurückführt......
Gruß Andreas

Answer 3

Hallo Andreas,

habe Deine Formel mal etwas optimiert und hoffe, dass ich deren Sinn richtig verstanden habe.


=WENNFEHLER(INDEX(Veränderungen!$L:$L;KKLEINSTE(WENN(INDIREKT("'Veränderungen'!A1:A400")=B$59;ZEILE(INDIREKT("1:400")));ZEILE(A1)))&" / "&INDEX(Veränderungen!$H:$H;KKLEINSTE(WENN(INDIREKT("'Veränderungen'!A1:A400")=B$59;ZEILE($1:$400));ZEILE(A1)))&" / "&INDEX(Veränderungen!$I:$I;KKLEINSTE(WENN(INDIREKT("'Veränderungen'!A1:A400")=B$59;ZEILE($1:$400));ZEILE(A1)));"")

Gruß
Rainer

Answer 4

Hallo Rainer,

du hast den Nagel auf den Kopf getroffen! Ganz toll, es funktioniert. Jetzt muss ich mir nur noch was einfallen lassen, wie ich die Formel auf die anderen Zellen kopiere, das geht offenbar nicht ohne weiteres.....
Vielen Dank!!
Andreas

Answer 5

Hallo Andreas,

die Formel kannst Du problemlos nach unten ziehen.

Oder welche anderen Zellen meinst Du?

Gruß
Rainer

Answer 6

Hallo
wenn ich nach unten ziehe wird jeweils ....ZEILE(A1) durch ....ZEILE(#BEZUG!) ersetzt. Das$" vor der Zeilennummer hatte ich schon entfernt.
Gruß

Answer 7

Hallo Andreas,

dann machst Du etwas falsch.

Bei meiner geposteten Formel wird aus Zeile(A1) definitiv Zeile(A2)

Gruß
Rainer

Answer 8

Hallo Rainer,

das "A1" muss in dieser Spalte in jeder Zeile stehen bleiben. In der nächsten Spalte suche ich dann das zweite Vorkommen von B59 und dann steht anstelle A1 dann A2.
Aber ich habe verschiedenen Varianten des Kopierens probiert, das A1 wird jedesmal durch 'FEHLER! ersetzt. Komischerweise kann ich das in der Formel auch nicht durch "Suchen/Ersetzen" korrigieren...

Deine Formel stimmt und löst genau mein Problem, nur jetzt muss ich sie irgendwie in die anderen Felder "AM3:AP250" bringen.....

Answer 9

Hallo Andreas,

Du sprichst oftmals in Rätseln, das war auch der Grund, dass ich eine Beispielmappe angefordert habe.

Meine Formel ist so ausgelegt, dass sie vertikal kopierbar ist.

Du kannst sie in eine beliebige Zelle schreiben und nach unten ziehen.
Du kannst sie nicht horizontal verschieben.

Angenommen die Formel steht in Spalte A und Du benötigst sie in Spalte AM, dann kopiere sie aus A1-ohne dem Gleichheitszeichen-, füge sie in AM1 ein, setze das Gleichheitszeichen wieder davor und bestätige mit Strg+Umschalt+Enter.

Nun kannst Du sie auch in AM beliebig weit nach unten ziehen.

Anhand einer Beispielmappe könnte ich sie auch so umstellen, dass sie auch horizontal kopierbar ist.

Gruß
Rainer

Answer 10

Hallo Rainer
Danke für deine Geduld! Du hast ja Recht, das Problem lässt sich kaum mit Worten beschreiben . dazu kommt, dass tatsächlich für mich nicht nachvollziehbare Probleme entstehen, die
eigentlich banal sind wie das Kopieren oder suchen/Ersetzen. Ich bastle mal eine Beispielmappe zusammen und sende diese.....
Danke und Gruß