Formel -> Bereich ändern mit Wert aus Zelle

[o0Julia0o]

hu, ich möchte in einer Formel den Bereich ändern auf einen Wert in einer Zelle. Geht das? Ich habe Zelle A1 einen Wert stehen, z.B. 50. Dieser kann sich verändern.
statt:
=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$5;$A$3:$A$40)=0)*ZEILE($3:$40););ZEILE(A1))););"")
soll das Ergebnis dann so sein:
=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$5;$A$3:$A$50)=0)*ZEILE($3:$40););ZEILE(A1))););"")
So klappt es nicht, aber das ist mein Lösungsansatz:
=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$50;$A$3:$A$&A1)=0)*ZEILE($3:$50););ZEILE(A1))););"")"


Oder geht das nur per VBA? So sage ich VBA, dass es etwas in eine Zelle schreiben soll:
Worksheets("Rech").Range("A3").FormulaLocal = "=" & "=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$5;$A$3:$A$40)=0)*ZEILE($3:$40););ZEILE(A1))););"")"

Jetzt habe ich ja in Zelle A1 einen Wert stehen, z.B. 50. Dieser kann sich ja wie gesagt verändern. Kann ich den Wert in der Formel durch VBA mit der 50 aus A1 ersetzen lassen, so dass aus der 40 eine 50 wird?

lieben Dank!

[snickers]

So klappt es nicht, aber das ist mein Lösungsansatz:

=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$50;$A$3:$A$&A1)=0)*
ZEILE($3:$50););ZEILE(A1))););"")


Möglicherweise meinst Du:

=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$50;$A$3:INDEX($A:&A;$A1))=0)*
ZEILE($3:$50););ZEILE(A1))););"")

[Jockel]

Hallo, und ZEILE($3:$50) bleibt unverändert...?

[snickers]

Hallo Jockel, 

schau Dir mal die anderen Formelbestandteile an ... insbesondere ZEILE, welches mit einem Arg. von ZÄHLENWENN "korrespondieren will". Zählarg. und Krit. von ZÄHLENWENN können auch nicht zueinander passende Bereiche sein.

Trotzdem könnte es sein, dass Du am Ende zumindest einen Mit-Fehler genannt hast, da TE das Rotgenannte nicht durchhält.

Ohne Tabelle kann man schlecht testen. Bin auch zu faul, entsprechend zu raten oder nachzubauen, sondern muss mich darauf verlassen, dass die Formel oben funktionierte ...

Hallo Julia,

... daher die Gegenüberstellung zweier Deutungen in beabsichtigter etwas kleinerer Schrift (!), damit sie untereinander stehen können und Julia (keine älteren Herren! Für Euch gibt es Strg-+ an dieser Stelle) erkennt, was ich meine:

=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$50;$A$3:INDEX($A:&A;$A1))=0)*ZEILE($3:$50););ZEILE(A1))););"")

=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$50;$A$3:INDEX($A:&A;$A1))=0)*ZEILE(A$3:INDEX(A:A;A1)););ZEILE(A1))););"")

[o0Julia0o]

[quote pid='100351' dateline='1511312953']

Code:

=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$50;$A$3:INDEX($A:&A;$A1))=0)*ZEILE($3:$50););ZEILE(A1))););"")

=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN($B$3:$B$50;$A$3:INDEX($A:&A;$A1))=0)*ZEILE(A$3:INDEX(A:A;A1)););ZEILE(A1))););"")

[/quote]

Danke euch! Alle beiden genannten Formeln nimmt Excel gar nicht an. Es stört sich jeweils am "$A".

Ich habe die Datei mal angehangen. Vielleicht hilft, dass ja mein Problem zu verstehen: 
  Sieger2.xlsx (Größe: 12,23 KB / Downloads: 15)

[Elex]

Hi,

teste mal die leicht geänderte Variante.


=WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN(B$3:B$16;INDEX(A$3:INDEX(A:A;$A$1);))=0)*ZEILE(A$3:INDEX(A:A;$A$1)););ZEILE(A1))););"")

[o0Julia0o]

..ganz schön komplex - das ergibt jedenfalls 0, anstelle eines Namens aus der Liste zuvor:
   

[Elex]

Hi

Schreibe einmal in A1 die Zahl 12 und du siehst erst mal dass die Formel funktioniert.
 
Du hast bisher nicht verraten was du dir eigentlich davon versprichst wenn du die rote Stelle in #1 durch einen Bezug auf A1 flexibel machst.
 
Die Anpassung der Formel hat folgende Auswirkung.
Momentan ist es so das der letzte Name in Zeile 12 steht. Deshalb muss der Bezug in der Formel an der roten Stelle mit 12 enden. Das war bisher fix in der Formel und ist nun durch A1 = 12 anzupassen. Hast du jetzt eine Liste mit mehr oder weniger Namen kannst du A1 anpassen. Letzter Name in Zeile 42 dann A1 = 42.

Gruß Elex

[o0Julia0o]

ahjo, übersehen. In A1 kann ich das ja sogar automatisch machen - so: =16-ZÄHLENWENN(A3:A16;"")

Ich habe das versucht in die Formel zu integrieren, dann müsste nix mehr in A1 stehen, aber das funktioniert nicht.

Warum ich den Wert flexibel benötige? Weil sich die Anzahl der Namen verändern kann. Ich habe ja von E1:E16 wieder neue Namen - wo dann einer von den vorherigen fehlt. So ist die Formel dann auch kopierbar. Aber auch in A1:A16 kann sich die Anzahl der Namen ändern. Dann muss ich die Formel nicht jedes Mal ändern.


  Sieger2b.xlsx (Größe: 12,46 KB / Downloads: 1)

[Elex]

Hi,

In C3 =WENNFEHLER(INDEX(A:A;1/(1/KGRÖSSTE(INDEX((ZÄHLENWENN(B$3:B$16;INDEX(A$3:INDEX(A:A;SUMMENPRODUKT((A$3:A$16<>"")*1)+2);))=0)*ZEILE(A$3:INDEX(A:A;SUMMENPRODUKT((A$3:A$16<>"")*1)+2)););ZEILE(A1))););"")