Nur zur Erinnerung, noch mal die Formeln aus meinem letzten Text.
In BO14
Code: Alles auswählen
=WENN(ISTFEHLER(VERGLEICH(1;AI28:AI44;0));"x";VERGLEICH(1;AI28:AI44;0))
Code: Alles auswählen
=WENN(BO14="x";"-";INDEX(C28:C44;BO14))
1.
Würde man sie nach unten "ziehen" beziehungsweise nach unten kopieren, dann ändern sich alle Zellbezüge, seis für eine einzelne oder für einen Zellbereich.
2.
Durch Punkt 1 würden da auch nicht nachvollziehbare Ergebnisse dabei rauskommen.
Was wir brauchen ist eine möglichkeit das sich die Zellbereiche automatisch so anpassen, das nach dem ersten Treffer dieser nicht mehr mitgezählt wird, und sich gleichzeitig der Zellbereich "Dynamisch anpasst".
Würde man anstatt
Code: Alles auswählen
VERGLEICH(1;AI28:AI44;0)
Code: Alles auswählen
VERGLEICH(1;AI$28:AI$44;0)
Was wir aber bräuchten wäre etwas in der Art, das in der nächsten Zelle unterhalb dieser Formel der Zellbereich sich automatisch ändert, was theoretisch so aussehen würde.
Code: Alles auswählen
VERGLEICH(1;AI32:AI44;0)
Nun, da kommt jetzt die INDIREKT zum Einsatz.
In BO14 steht ja als Beispiel die 3.
Und diese 3 ist die Basis für die neue indirekte-Zelladressierung.
Mit INDIREKT kann man nicht nur eine Zelle, sondern auch einen Zellbereich anders adressieren.
Und das bauen wir jetzt mal wegen der Verständlichkeit stückchenweise auf.
Nehmen wir mal etwas ganz leichtes, für einen Zellbereich.
In irgendeinem Tabellenblat an irgendeiner Stelle einfach mal das hier eingeben.
Code: Alles auswählen
="AI"&3&":AI44"
Das ist jetzt eine stark vereinfachte Textausgabe, die absolut reine weg gar nichts macht.
Setzen wir jetzt aber INDIREKT davor, also so
Code: Alles auswählen
=INDIREKT("AI"&3&":AI44")
Wir können aber auch innerhalb von INDIREKT eine Berechnung durchführen, z.B. so
Code: Alles auswählen
=INDIREKT("AI"&SUMME(B1:B2)&":AI44")
Code: Alles auswählen
="AI"&SUMME(B1:B2)&":AI44"
Dieser Text, welcher ja eigentlich ein Zellbereich darstellt, ist für uns Menschen natürlich zu lesen. Aber Calc kann damit gar nichts anfangen, und deshalb wird dieser "Text" an INDIREKT übergeben.
Eins muss man sich aber noch merken.
Bei INDIREKT("AI"...":AI44") wird alles was in doppelten Anführungszeichen "" steht, beim kopieren egal in welcher Richtung nicht von Calc geändert/angepasst. Und so wird aus ":AI44" NIE UND NIMMER ":AI66" wenn man die Formel wo anders hinkopiert (nur mal so als Beispiel).
Jedoch der Teil &SUMME(B1:B2)& der passt sich automatisch an, da es sich um eine Calc-Funktion handelt und sie nicht zwischenn Anführungszeichen steht (würde dort auch keinen Sinn ergeben).
Und eigentlich ist damit schon der Grundstein für einen "Dynamischen Zellbereich" gelegt.
Da wir ja in BO14 ja wie schon erwähnt eine Zahl stehen haben, die 3, beziehen wir uns jetzt auch genau auf diese Zelle. Und deshalb geben wir jetzt im Blatt *Crew* in der Zelle BO16 diese Formel ein.
Code: Alles auswählen
=VERGLEICH(1;INDIREKT("AI"&SUMME(BO$14:BO14)+28&":AI44");0)
Code: Alles auswählen
&SUMME(BO$14:BO14)
Code: Alles auswählen
&SUMME(BO$14:BO16)
Und dort steht noch etwas in der Formel was vielleicht irretiert, und zwar: +28
Nun, wenn BO14 die 3 ausgibt, dann kann man wohl damit via INDIREKT einen Zellbezug aufbauen, der aber einen falschen Bereich ergibt. Denn der würde nämlich dann wie folgt lauten: AI3:AI44
Okay, es ist wohl die gleiche Spalte, was schon mal sehr wichtig ist, aber das Ergebnis wäre Falsch.
Angenommen BO14 würde nicht die 3, sondern die 2 ausgeben, dann würde die Suchspalte von AI2:AI44 gehen und VERGLEICH würde auf der Suche nach der 1 auch einen Treffer finden. ABER dieser Teffer wäre die Zelle AI2, weil dort in AI2 auch eine 1 steht. Und genau aus diesem Grund müssen wir einen Korrekturfaktor einbauen, so das der für uns wichtige Zellbereich wieder da anfängt wo er anfangen soll. Und dieser Korrekturfaktor ist die +28.
Und wenn man jetzt wieder sagt, das in BO14 die 3 steht, so ergibt BO14 + Korrekturfaktor (3 + 28) gleich 31. Und schon ergibt
Code: Alles auswählen
="AI"&SUMME(BO$14:BO14)+28&":AI44"
Und wenn wir diese Formel
Code: Alles auswählen
=VERGLEICH(1;INDIREKT("AI"&SUMME(BO$14:BO14)+28&":AI44");0)
Code: Alles auswählen
=VERGLEICH(1;INDIREKT("AI31:AI44");0)
Jetzt müsste eigentlich verständlicher sein wie der "Dynamische Zellbereich" sich aufbaut, hoffe ich zumindest
So, und nun müssen wir wieder einen Grundstein für die Fehlerüberprüfung einbauen, das dann so aussieht.
Code: Alles auswählen
=WENN(ISTFEHLER(VERGLEICH(1;INDIREKT("AI"&SUMME(BO$14:BO14)+28&":AI44");0));"x";VERGLEICH(1;INDIREKT("AI"&SUMME(BO$14:BO14)+28&":AI44");0))
Okay, auf zum nächsten Punkt, beziehungsweise Zelle.
Das war ja jetzt nur die Zellkoordinate. Jetzt wollen wir aber auch einen passenden Namen zu dieser Koordinate haben.
Den Namen wollen wir uns ja jetzt in BI16 ausgeben lassen, was dann wie folgt aussieht.
Code: Alles auswählen
=WENN(BO16="x";"-";INDEX(INDIREKT("C"&SUMME(BO$14:BO14)+28&":C44");BO16))
Die beiden Formeln in BI16 und BO16 können nun nach BI18 und BO18 kopiert werden.
Ich gebs ja zu, das es fürs erste mal sich alles sehr schwer anhört, aber wenn ich daran denke was sich alles für Leute in einem anderen Forum an so einer ähnlichen Problematik beteiligt hatten, denn der Grund war ich der wissen wollte wie man das lösen kann, und das diese Problematik auch nicht an einem Tag erledigt war, so ist das jetzt hier ein schöner kleiner Crash-Kurs geworden.
Und nun lege ich mich ganz gemütlich zurück, und warte mal auf Eure Reaktionen.

Gruß
balu