[Excel] In einer Formel Bezug nehmen auf Formel anderer Zelle

[.mojo]

Hallo.

Ich steh grad etwas auf dem Schlauch..


Angenommen, ich will in die Zelle A1 in Exel ein Formel erstellen, die zB Zelle B20 mit Zelle A2 addiert (=B20+A2), wobei ich die Zeilennummer, also 20 aus der Formel einer anderen Zelle beziehen will.
Also angenommen die Formel der Zelle D5 lautet "=H7" (also der Wert der in H7 steht wird auch in D5 geschrieben). Wie kann ich also nun die Zeilennummer der ersten Variable (20) meiner Summenfunktion aus A1 (=B20+A2) aus einer Zeilennummer einer Zellenangabe in der Formel der Zelle D5 beziehen? Im Beispiel also, die 7 aus der Variable H7, sodass meine Formel in diesem Fall "=B7+A2" lauten würde.

Wenn die Zelle D5 also irgendwann einmal nicht mehr auf H7 verweist, sonder auf C8, soll sich die Formel in A1 ändern in "=B8+A2".

Also falls doch jemand bisher noch nicht aus meienr amateurhaften Beschreibung kopfschüttelnd ausgestiegen ist, wäre ich dankbar für jeden Rat.


Hab jetzt ne gute Stunde mit Google nach einer Lösung gesucht, leider erfolglos.
Arbeite ansonsten nur sehr eingeschränkt mit Excel, daher weiß ich noch nicht einmal so recht nach was genau ich suchen muss..

ICh hoffe, mein Vorhaben ist überhaupt realisierbar..


MfG
mojo

 

[GMG-CC]

Hmmm mojo,

irgendwie erinnert mich das Geschriebene an einen Gordischen Knoten ...

Aber OK, du willst irgendwie herauskriegen, in welcher Zeile ein Zelle mit einem Ergebnis steht, richtig? Und das willst du dann irgendwie weiter verwerten, richtig? Wieso, wehalb, warum, ich frage nicht nach

Als Denkansatz: Du willst aus B7 die Zeile extrahieren, welche als erstes Argument angegeben ist. Das würde ich so anfangen:

• B20 bekommt einen Bereichsnamen, beispielsweise "Bezug1" (ohne Anführungen natürlich)
• Die Formel in A1 ist dann: =Bezug1+B2
• Um nun die Zeile des ersten Arguments zu bekommen, schreibst du =zeile(Bezug1 in eine Zelle und du bekommst die 20 als Ergebnis.
• Soll sich die Zeile 20 als Bezug ändern, dann benennst du einfach eine andere Zelle mit dem Bereichsnamen Bezug1 und der Weret der Zeile wird automatisch angepasst.

Ich bin aber fest davon überzeugt, dass sich das anders lösen lässt, wenn ein begreifbares, konkretes Beispiel in einer Excel-Tabelle vorliegt.

Ach ja, die Funktion INDIREKT() könne dir auch weiter helfen.

 

[Mr. Snoot]

Sofern der Vorschlag von cpt. Nemo dir nicht weiterhilft, wäre ich auch mal für eine Beispieltabelle, denn wirklich folgen kann ich deiner Beschreibung nicht

 

[.mojo]

Schonmal danke für Antworten. Das du nicht folgen kannst, ist verständlich. Zugegeben, die Beschreibung ist suboptimal, zumal ich grade gesehen habe, dass mir ein Tippfehler unterlaufen ist.. Spätestens der sollte auch den letzten dann vollends verwirren
und zwar hieß es bis eben noch

Wenn die Zelle D5 also irgendwann einmal nicht mehr auf H7 verweist, sonder auf C8, soll sich die Formel in A1 ändern in "=B7+A2".

an der hervorgehobenen Stelle muss eine 8 stehen, also die Zeilennummer einer Zelle in der Formel einer anderen Zelle (ohjeh..)

Im Grunde dreht es sich um folgendes, so ist es vielleicht besser verständlich:
Eine Variable der Formel einer Zelle X (also bei einer angenommen Formel von "=D2+E2" zb die Variable E2) soll sich an einer Stelle (als Beispiel die Zeile - also die 2 aus E2) automatisch danach richten, welche Zeilennummer eine Variablen in einer anderen Formel der Zelle Y hat (zb"=G10-G11". nehmen wir als Beispiel G11).


Also ich habe 2 Zellen:
1.) A1=D2+E2
2.) B1=G10-G11

Die Zeile des 2. Summanden meiner ersten Formel (die 2 aus E2) soll sich nun immer nach der Zeile richten, die der Subtrahent aus der 2. Formel in diesem Moment hat.
Das sich die Formel aus A1 also ändert in "=D2+E11. Das E soll imer bestehen bleiben, nur die Zeile soll sich dynamisch anpassen, wenn ich also die Formel in B1 ändere in "=G10*M15", soll sich A1 sofort ändern in "=D2+E15". Welche Spalte die Variable hat soll keine Auswirkungen haben.


ICh glaube so ist es dann doch etwas leichter verständlich..
Sorry, war heute morgen noch übrig von gestern

Auf die INDIREKT Funktion bin ich auch schon gestoßen, habe aber glaube ich nicht richtig verstanden wie man die richtig einsetzt. Wenn diese für mein Prblem geeignet wäre, wäre ich dankbar wenn man das mir anhand des obigen Beispiels erklären könnte.
ICh muss halt dazu sagen, dass ich in excel ansonsten im äustersten Fall gerade mal wirklich nur etwas in eine Tabelle eintrage..

Hier geht es eigentlich um ein Tippspiel, das mein Bruder anlässlich der WM gemacht hat, dass eben lediglich die Tipps eingetragen hatte. ICh weiß dass es zwar zig Excel-Tippspiel-sheets im Netz gibt, aber das hat mich dann spontan interessiert und so hab ich es einfach mal etwas erweitert.
Hier geht es nun darum, dass die Spiele in der Übersicht nach den Gruppen geordnet sind, und nicht nach der tatsächlichen Reihenfolge, wie sie ausgetragen werden.
Da ich eine Trend/Tendenz/"Form" Funktion einbauen wollte (und auch habe), die eben mittels simplen Pfeilen (hoch, runter oder zur Seite) einen Anstieg oder Abfall der Punktausbeute pro Tipp bei allen Mitspielern anzeigen soll, musste ich die Spiele also erstmal in Reihenfolge bringen. Dies habe ich mittel simplem "Zellen kopieren" erledigt und die Zeilen eben so getauscht das sie Paarungen so auffeinanderfolgen wie sie es eben bei der WM tun.
(Hier, und auch an vielen anderen Stellen bin cih sicher mir zuviel arbeit gemacht zu haben, ich weiß dass man mit Excel sher viel machen kann, aber ich hab es eben jetzt erstmal so gemacht um überhaupt mal ne Grundlage zu haben)
MEin Problem ist nun, dass alle Daten der einzelnen Paarunge 1:1 aus der ursprünglichen Auflistung gezogen werden.
Nun macht mir Exel bei den Ergebnisfeldern der Paarungen überall eine 0 hin, wenn in der ürimärtabelle noch kein Ergebnis eingetragen wurde.
Da bin ich dann auf NICHTLEER gestoßen, was mir auch weiterhilft.
Da ich das aber aus Interesse an der Sache mache, wil ich nicht einfach alle Ergebnisfelder mittels NICHTLEER manuell anpassen, sondern das an einer Stelle machen und dann einfach die Formel mittels dieses schwarzen + auf die anderen Ergebniszellen übertragen. Da die PAarungen aber anders geordnet sind, würde dass alles durcheinander bringen.
Daher will ich den Zeilenbezug im Verweis der Ergebnisfelder gleich dem Zeilenbezug einer Zelle setzen, die in der gleichen Zeile liegt und für jede Paarung schon auf die richtige Zeilennummer in der ursprungstabelle verweist.
Das Ergebnisfeld verweist immer auf Spalte H (da die ergebnisse alle untereindander stehen) und die Zeile soll gleich der Zeile aus zb dem Verweis des Feldes "Durchschnittliche Punkte pro Tipp" einer jeden PAarung sein. Soweit ich nicht einen Denkfehler drin habe müsste ich diese universele Formel (mit WENN(ISTLEER) Zusatz) dann mit dem schwarzen + auf alle Ergebnisfelder aufziehen können und es sollte dann nichts durcheinander sein und bei leerem Ergbnisfeld in der ursprungstabelle sollte auch das Ergebnisfeld in der Hilfstabele leer bleiben und nicht "0" als Wert haben.



Auch hier bin ich fast sicher dass ich mich unfassbar umständlich anstelle. Sicher geht es mit irgendnem Kniff alles viel einfacher und in 3 Klicks gemacht.. Da ich mich in meinem jugendlichen Leichtsinn spaßeshalber aber einfach mal frei ans Werk gemacht habe, stehe ich halt nun wo ich eben stehe



Jedenfalls habe ich heute in der Morgendämmerung nebts einigen Statistiken (evtl werde ich noch stat-pages der einzelnen Tipper bauen, mit grafischer Darstellung verschiedener stats, usw...) schonmal ein Ranking aller Tipper auf die Reihe gekriegt, dass bei gleichstand 2er Tipper zunächste die Anzahl richtiger Ergebnisse heranzieht und wenn auch dort gleichsatnd herrscht noch die "Form" (da mir erstmal nichts besseres eingefallen ist - wobei das eh nur aus Spaß ist, für den letztendlichen ausgang des Tippspiels sind nur die Punkte relevant).
Aktualisiert wird es derzeit noch manuell über nen vbObjekt, was mich zu meiner nächsten Frage bringt (hier habe ich noch nicht gegoogled, aber wenn wir schonmal dabei sind..):

Wie erreiche ich, dass wenn in bestimmte Zellen etwas eingetragen wird, automatisch ein vba script, bzw Makro abläuft?



Das aus der Sache jetzt so ein Aufwasch wurde war nicht beabsichtigt. Aber ihr habt ja danach gefragt.

 

[Mr. Snoot]

Also ich habe 2 Zellen:
1.) A1=D2+E2
2.) B1=G10-G11

Die Zeile des 2. Summanden meiner ersten Formel (die 1 aus B1) soll aixh nun immer nach Zeile richten, die der Subtrahent aus der 2. Formel in diesem Moment hat.
Das sich die Formel aus A1 also ändert in "=D1+E11. Das E soll imer bestehen bleiben, nur die Zeile soll sich dynamisch anpassen, wenn ich also die Formel in B1 ändere in "=G10*M15", soll sich A1 sofort ändern in "=D2+E15. Welche Spalte die Variable hat soll keine Auswirkungen haben.


ICh glaube so ist es dann doch etwas leichter verständlich..

Jein; ich glaube da sind einige Fehler drin.

Deine erste Formel ist A1=D2+E2. Du schreibst: Die Zeile des 2. Summanden meiner ersten Formel (die 1 aus B1). B1 kommt in deiner ersten formel nicht vor, und ist auch in der zweiten Formel kein Summand.

Und dann noch: Das sich die Formel aus A1 also ändert in "=D1+E11.Wieso D1?




Du willst wohl:

die Zeile
des zweiten Summanden
aus Formel 1

soll gleich sein

der Zeile
des Subtrahenden
aus Formel 2​

Wobei sich Formel 2 aber quasi beliebig ändern kann? Automatisiert wüsste ich da nichts. Aber wo kommt denn Formel 2 her und wer ändert diese?


Angenommen du hast als 2. Formel =X3+Y2 und willst daher als 1. Formel =A5+B2, dann könnte man theoretisch beide Formeln ändern:

1. Formel: =X3+INDIREKT("Y" & T1)
2. Formel: =A5+INDIREKT("B" & T1)

wobei in T1 die gewünschte Zeile steht, also in diesem Fall 2. Dann hättest du immer die gleiche Zeile ...




P.S. weiter hab ich noch nicht gelesen

 

[.mojo]

achjeh, ja, ich hatte den Text schon geschrieben, dann aber das Beispiel nochmal abgeändert. Dabei ist mir dieser Absatz wohl durch du Lappen gegangen.. Ich kann nur hoffen dass nun alles passt.. Ich bin immer noch schwer mitgenommen..

Naja, wie auch immer, ich denke mit

1. Formel: =X3+INDIREKT("Y" & T1)
2. Formel: =A5+INDIREKT("B" & T1)

hast du mir schon geholfen.
Wenn ich das also richtig verstanden habe, kann man nicht direkt Formelbestandteile abgreifen/auf diese verweisen, sondern muss den gewünschten Teil der Formel erst in ein Zwischenfeld auslagern?
Aber müsste man den Inhalt in der Zwischenzelle denn dann nicht auch direkt als Teil einer anderen Formel verwenden können?
Ich vermute nun du meinst es eher so, dass ich in T1 die jweiligen Zeilen manuell eintrage.

Das ist jab aber eigentlich des Pudels Kern. Ebendies wollte ich ja gerade vermeiden.


Das was in die Zwischenfelder käme, habe ich ja eigentlich schon in anderen Feldern enthalten. Nur eben nicht im Feldinhalt, sondern als Teil der Feldformel.
Ich versuche also nicht den Inhalt einer Zelle in einer Formel zu nutzen, sondern einen Teil der Formel einer Zelle. Ich will also eine Formel erstellen, die eine Zellenangabe enthält, welche nur die Spalte festeingetragen hat. Die Formel soll also an dieser Stelle quasi eine Abfrage gegen die Formel einer anderen Zelle ausführen um mit diesem Ergebnis die bisher unvollständige Zellenangabe zu vervollständigen. Also um im Beispiel zu bleiben

A1=D2+E[Ergebnis der Abfrage "wie ist der Zeilenwert des Subtrahenden der Formel die mit Zelle B1 verknüpft ist"]

Initial war erstmal die Frage, wie ich es hinkriege, dass in einer Zelle die nur das selbe azeigen soll wie eine andere nicht "0" steht, wenn die Zelle auf die verwiesen wird noch keinen Inhalt hat. Da hatte ich dann ISTLEER ausgegraben.
DA aber meine Hilfstabelle anders sortiert ist als die ursprungstabelle (auf die alle Felder verweisen) würde es nicht gehen, wenn ich die ISTLEER Formel an einer Stelle erstelle und dann auf alle übrigen Felder anwende, indem ich das Feld mittels dieses schwarzen + über die restlich drüberziehen. Dabei zählt Excel die Zeilen und Spalten in der Formel ja immer nur soweit hoch (oder runter) wie die Zielfelder in Zeilen und Spalten vom Quellfeld abweichen. Das würde aber aufgrund der inkonsitenten Reihenfolder der beiden Tabellen zu falschen Werten führen.
Da dachte ich mir dann eben, man könnte dies umgehen, wenn man nicht absolut verweist, sondern die Verweisadresse von einem zu jedem Ergebnisfeld gleichweit entferntem Feld abfragt. So müsste das "Aufziehen" der Formale auf andere Felder zu den richtigen Ergebnissen führen.
Und hier kommt eben die Sache ins Spiel, dass die nötige Information nihct im Inhalt der Nachbarzelle steckt, sondern in deren Formel.


Warum ich das nihct gleich so formuliert habe ist mir schleierhaft.
Ich schiebs auf die Feierei...

 

[Mr. Snoot]

Ich denke mal, dass du vermutlich nur mit VBA weiterkommst (je nachdem, wie deine Tabelle aussieht).

Ich wüsste jedenfalls nicht, wie du per Formel an die gewünschten Informationen kommst.

 

[.mojo]

gut, das habe ich mir im Verlauf des Threads dann schon beinahe selbst gedacht.
Dann werde ich mich mal mit vba beschäftigen.
Ich deute deine Antwort so, dass du keine Möglichkeit kennst es direkt per Exel Formel zu machen, aber weißt, dass es mit vba geht. Ist das korrekt?

Dalls doch noch jemand eine Antowrt weiß, kann er sie ja gerne noch posten.


MfG und Dank für die Geduld
mojo


Edit: Bliebe noch die Frage

wie erreiche ich, dass wenn in bestimmte Zellen etwas eingetragen wird, automatisch ein vba script, bzw Makro abläuft?

 

[Mr. Snoot]

Ich weiß leider nicht, wie deine Tabelle aussieht, aber mit VBA hast du halt viel mehr Möglichkeiten als mit Formeln.

Du sagst ja, du änderst die eine Formel und dementsprechend soll die andere Formel sich ändern. Die Änderung muss ja nach irgendwelchen Kriterien erfolgen - und dass könnte man via VBA halt ggf. automatisieren und auf die andere Formel übertragen.



Zu dem automatischen Makrostart, probier mal das:

Private Sub Worksheet_Change(ByVal Target As Range) 
  If Target = Range("A1:A10") Then Call dein_makro
End Sub

 

[.mojo]

ah ok, danke. werde das mal versuchen.