Wert in gefiltertem Bereich in Zelle darstellen

[ari-2001]

Hallo,
ich habe in einer Tabelle, beginnend ab c5 eine Reihe Namen stehen.

Nun möchte ich in einer unter der Tabelle in der Zelle J370 den ersten Wert darstellen, wenn die Tabelle gefiltert ist.

Dies mache ich mit der Formel:

={INDEX(C:C;MIN(WENN(TEILERGEBNIS(3;INDIREKT("c"&ZEILE(5:367)))*ZEILE(5:367)>0;TEILERGEBNIS(3;INDIREKT("c"&ZEILE(5:367)))*ZEILE(5:367))))}
(Index Formel)

Obwohl die Zelle nicht im Bereich liegt, steht immer Zirkelbezug in J370, obwohl die Formel richtig berechnet wird.
Woran kann das liegen??

Viele Grüße
Andreas

[neopa]

Hallo Andreas,

Deine Formel ist korrekt, wenn es in der Datei nur die Daten in C5:C67 und die Formel in J370 gäbe.

Unabhängig davon, dass die Formel korrekt ist, lasse ich eine derartige Formel mit INDIREKT() nicht über gesamte Zeilen oder Spaltenbereiche wirken. Ich würde die Formel so schreiben:

Code:

=INDEX(C:C;MIN(WENN(TEILERGEBNIS(3;INDIREKT("c"&ZEILE(C5:C367)))*ZEILE(C5:C367)>0;
TEILERGEBNIS(3;INDIREKT("c"&ZEILE(C5:C367)))*ZEILE(C5:C367))))

Aber das allein wird Dir möglicherweise bzgl. Deines Problems nicht weiterhelfen, oder?

Wenn nicht, dann folgendes:
Sicherlich hast Du in Deiner Tabelle noch eine Reihe von anderen Formeln, die Ursache der Fehlermeldung sein können. Da Du Deine Datei wahrscheinlich im Original nicht einstellen kannst, ist es fast unmöglich, Dir bei der Fehlersuche zu helfen.

Ich kann nur raten wie man vorgehen könnte. Ich würde mich Schritt für Schritt an die Fehlerursache herantasten.

Nachfolgendes nimm in einer Dateikopie vor.
- lösche zunächst die anderen Tabellenblätter (so vorhanden) Taucht der Fehler noch immer auf, dann weißt Du, dass Du den Fehler im Tabellenblatt suchen musst. Dann siehe unten wie weiter.
Taucht der Fehler nicht mehr auf. Lösche die Hälfte der anderen Tabellenblätter. Wenn danach der Fehler nicht mehr vorhanden sein sollte, lies die Dateikopie wieder ein und lösche von dem anderen Tabellenblättern die Hälfte. Je nachdem verfahre analog weiter, bis Du das Tabellenblatt gefunden hast, in dem die Ursache zu suchen ist.

Liegt der Fehler dort oder auch im gleichen Tabellenblatt wie J370 begründet, verfahre wie folgt:
- ich geh davon aus, dass in C5:367 keine Formeln stehen, oder?
- wenn es nur wenige weitere Formeln außer J370 im Tabellenblatt gibt, schau Dir deren Auswertungsbereiche an oder lösche die einzeln bis es keine Fehlermeldung nehr gibt.
- gibt es viele Formeln lösche zunächst eine Hälfte der Bereiche, wo Formeln vorhanden sind. Wenn danach der Fehler nicht vorhanden sein sollte, hole diese wieder zurück und lösche von dem anderem Bereich die Hälfte. Danach verfahre mit dem Bereich in dem der Fehler begründet sein sollte analog weiter. Also immer ca. die Hälfte aus der Untersuchung ausschließen, bis Du die Fehlerursache gefunden hast. Dann weißt Du zumindest erst einmal, wo Du bei der Formeldefinition neu überlegen musst.

Vielleicht hilft es Dir ja.  Wenn nicht, könnte evtl. eine anonymisierte Datei weiterhelfen, die Du hier einstellst.

Viele Erfolg.

[ari-2001]

Hallo Neopa,
du schreibst die Formel genau wie ich, oder sehe ich da bloß keinen eventuell vorhanden Unterschied?

Ich werde mal schauen, in c5-c367 stehen Formeln, die anschliessend den Namen ergeben, der wiedergegeben werden soll in J370.
Vielleicht liegt es daran.

Könntest Du mir deine Formel eventuell noch posten?

Vielleicht ist die von den Bezügen her besser, wenn die nicht über die ganze Spalte läuft.

Besten Dank im Voraus,

Andreas

[XOR LX]

Hallo,

Mit den neuen dynamischen Arrays in Excel 365, wenn Sie für den Parameter Zeile der INDEX funktion den Wert 0 festlegen, INDEX versucht die gesamte Spalte zurückzugeben.

Um 1048576 Zeilen zurückzugeben, muß mann in Zeile 1 beginnen, andernfalls wird ein #SPILL! Fehler gemeldet. Von eurem Beispiel es scheint, daß einen Zirkelbezug hat Vorrang vor ein #SPILL! Fehler.

Gruß