Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.04.2024 22:12:12
Datei Attribute auslesen
29.04.2024 20:24:42
Datum nach Tag Monat Jahr sortieren
29.04.2024 19:27:41
Farbvorkommen innerhalb einer Produktkategorie
29.04.2024 17:40:50
Formel Index mit Vergleich kombiniert mit Addition
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1908to1912
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1908to1912
1908to1912
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Verknüpfung Tabellenblatt über Zelle

Verknüpfung Tabellenblatt über Zelle
07.12.2022 19:30:00
Susanne
Hallo,
ich verzweifle an einer Verknüpfung einer relativ einfachen Formel zu einem anderen Tabellenblatt.
In der Datei https://www.herber.de/bbs/user/156576.xlsx habe ich meine Problemstellung kurz aufgebaut.
Ich möchte die Formeln
=SUMMENPRODUKT((Tabelle2!A1:F30=B6)*ZEILE(1:30)) und
=INDIREKT(ADRESSE(8;VERGLEICH(B4;Tabelle2!7:7;-1)+ZÄHLENWENN(Tabelle2!7:7;B4)-2)) über eine Zelle mit dem Wert "Tabelle2!" auf das andere Blatt zugreifen lassen und damit Zellen zur weiteren Verschachtelung verwenden. So kann ich sehr flexibel bleiben.
(Die zweite Formel funktioniert nicht mal so richtig, nur direkt auf dem Blatt.)
Normalerweise baue ich diese Bezüge mit Verketten auf, aber hier schaffe ich kein befriedigendes Ergebnis.
Ich bitte um eure Unterstützung, gerne auch über einen anderen Lösungsweg ohne Makro.
LG,
Susanne

40
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
07.12.2022 20:06:37
onur
Wo ist denn das Problem, in Formel 2 die Bezüge
Tabelle2!7:7
durch Indirekt zu erweitern?
Das Selbe mit Bezug
Tabelle2!A1:F30
in Formel1.
AW: Verknüpfung Tabellenblatt über Zelle
08.12.2022 10:23:40
Susanne
Hallo Onur,
vielen Dank für die rasche Unterstützung. Tut mir jedoch leid, ich stehe echt am Schlauch. Wo soll das INDIREKT hin? Ich bekomme nicht den gewünschten Wert raus.
=SUMMENPRODUKT((INDIREKT(VERKETTEN(B5;A1;":";F30))=B6)*ZEILE(INDIREKT(VERKETTEN(1;":";30))))
Das klappt leider nicht.
Im Anhang meine Versuche.
https://www.herber.de/bbs/user/156588.xlsx
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
08.12.2022 13:54:40
onur
Erkläre doch einfach mal genau, was die beiden Formeln berechnen sollen.
AW: Verknüpfung Tabellenblatt über Zelle
08.12.2022 15:49:58
Susanne
Diese Formel: =SUMMENPRODUKT((Tabelle2!A1:F30=B6)*ZEILE(1:30)) sucht nach dem Wort ITEM (B6) auf dem anderen Tabellenblatt (ich brauche das für die Lokalisierung der Zeile mit dem Inhalt Item).
Diese Formel: =INDIREKT(ADRESSE(8;VERGLEICH("Sales Value EUR";7:7;-1)+ZÄHLENWENN(7:7;"Sales Value EUR")-1)) soll mir die letzte Zelle mit dem Wert "Sales Value EUR" ausgeben und dann die Zelle darunter ausspucken (auch diese wird für weitere Berechnungen verwendet).
So funktionieren beide Formeln, nur jedoch nicht wenn ich sie mit einer Variable (verschiedene Tabellenblätter) ausstatte.
Ich kann den Bezug Tabelle2! nicht erfolgreich durch eine Zelle ersetzen.
Ebenso wenig wie ich die zweite Formel erfolgreich auf einem anderen Tabellenblatt ausführen lassen kann (letztendlich auch mit der Adresse als Variablen).
Ich bin echt nicht besonders gut im Schildern... aber das hast du ja bereits gemerkt.
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
09.12.2022 09:54:39
Susanne
Guten Morgen Onur,
vielen Dank für deine Unterstützung. Jedoch habe ich leichte Schwierigkeiten bei der Verwendung deiner Formeln.
Es ist ja so, dass die Tabelle2 letztendlich nicht das einzige Blatt ist und deswegen auch explizit nach den Inhalten gesucht und nicht manuell Zeile 7 und Spalte 4 verwendet werden soll. (Deswegen das Wort Item als Anker, weil dieses immer in der Zeile vorkommt, wenn auch mit unterschiedlichem Abstand.)
Für kürzere, performantere Formeln bin ich immer dankbar!
Brauchst du mehr Informationen von mir, um es verständlicher zu machen?
LG, Su
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
09.12.2022 10:28:29
onur
Das liegt wohl eher an deiner mageren Beispielsdatei. 

=@INDIREKT(ADRESSE(8;VERGLEICH(B4;Tabelle2!7:7;-1)+ZÄHLENWENN(Tabelle2!7:7;B4)-2))
sucht ja auch nur in Zeile 7.
Aber letztendlich wollte ich dir nur zeigen, WIE man INDIREKT benutzt - auf jeden Fall nicht so, wie du glaubst.
Wenn du meine oberen Formeln mit den unteren vergleichst, siehst du, wie es geht.
"Für kürzere, performantere Formeln bin ich immer dankbar! " - gerne, aber mit realistischerer Beispielsdatei.
AW: Verknüpfung Tabellenblatt über Zelle
12.12.2022 13:32:43
Susanne
Hallo Onur,
vielen Dank für deinen Support. Ich habe die Datei für dich nochmal etwas aufgebaut und mit Verweisen ausgestattet. Vielleicht macht es so mehr Sinn.
Bei der Formel mit dem Verweis auf die Zeilen 7, hab ich mir gedacht, dass ich diese später ersetze durch das Ergebnis samt Subtrahierung ("Zeile mit ITEM"-1)
der anderen Formel.
Letztendlich möchte ich ja dann auf die EUR-Umsätze zugreifen.
https://www.herber.de/bbs/user/156664.xlsx
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
12.12.2022 14:25:14
onur
Verstehe immer noch nicht, was du berechnen willst. Wie wäre es mit einem FUNKTIONIERENDEN Beispiel?
Deine Formel in C9, die angeblich funktioniert, spuckt schliesslich nur 0 (Inhalt Zelle H8 auf Blatt "Template") aus.
AW: Verknüpfung Tabellenblatt über Zelle
12.12.2022 14:38:29
Susanne
Diese Formel "=INDIREKT(ADRESSE(8;VERGLEICH(Template!B3;Tabelle2!7:7;-1)+ZÄHLENWENN(Tabelle2!7:7;Template!B3)-2))" findet ja auf "Tabelle2" das Ergebnis "Oct 20-Sep 21". Und ich würde gerne das Ergebnis von dem Blatt "Template" aus finden anstatt mit "Tabelle2! den Verweis von B (=Tabelle2!).
Berechnen erst gar nichts, nur erst mal finden, damit ich die "Adresse" verwenden kann.
Die Formel, welche die Null ausspuckt, geht ja eben nur auf dem Tabellenblatt, auf dem sich die Werte befinden. Das wollte ich durch das Textfeld ausdrücken.
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
12.12.2022 14:41:03
Susanne
*mit den Verweis von B4 (=Tabelle2!).
AW: Verknüpfung Tabellenblatt über Zelle
12.12.2022 16:54:34
Susanne
Onur deine beiden unteren Formeln werden von meiner 2013er Version leider nicht unterstützt.
Aber diese 

=INDIREKT($B$4&ADRESSE(C8;VERGLEICH($B$3;INDIREKT($B$4&"Z7";FALSCH);1)))
sieht ganz gut aus. Ich habe die 8 durch die Zelle C8 ersetzt.
Jedoch bräuchte ich jetzt noch die Z7 durch eine Zelle ersetzt, um es flexibel zu gestalten. Optional die Zelle C8-1
UND die Formel in C8, welche nach der Zeile mit Item sucht, ist ja auch nicht flexibel.
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
12.12.2022 17:18:41
onur
Die Formeln unten enthalten eigentlich nix, was nicht von Excel2013 unterstützt wird.
"...die Formel in C8, welche nach der Zeile mit Item sucht, ist ja auch nicht flexibel. "

=SUMMENPRODUKT((INDIREKT(Z4S2 & "Z1S1:Z30S6";FALSCH)=Z5S2)*ZEILE(Z1:Z30))

AW: Verknüpfung Tabellenblatt über Zelle
13.12.2022 10:42:13
Susanne
Onur, herzlichsten Dank! Nun finde ich die Zeilennummer mit dem Eintrag Item!
Tut mir leid, für die schwere Geburt.
Was noch auffällig war, dass deine Formel nur über die Z1S1-Einstellung einzusetzen war. Excel verdreht sonst sogar einen Bezug und macht aus Z5S2 ein ZS52.
Für mich funktioniert nun 

=SUMMENPRODUKT((INDIREKT(B4&"Z1S1:Z30S6";FALSCH)=B5)*ZEILE(Z1:Z30))
bzw 


=SUMMENPRODUKT((INDIREKT(B4&"A1:F30")=B5)*ZEILE(1:30))
Danke nochmal.
Anzeige
Gerne !
13.12.2022 10:45:05
onur
Ich arbeite nur mit Z1S1-Format, es ist fast alles wesentlich einfacher damit, wenn nicht sogar nur so möglich.
AW: Verknüpfung Tabellenblatt über Zelle
13.12.2022 10:44:29
Susanne
Bitte ohne Makro. Ich habe in der angehängten Datei es hoffentlich nochmal etwas veranschaulicht.
https://www.herber.de/bbs/user/156689.xlsx
Aus dieser Formel 

=INDIREKT($B$4&ADRESSE(D8;VERGLEICH($B$3;INDIREKT($B$4&"Z7";FALSCH);1)))
darf noch das 

"Z7"
entfallen. Dann ist alles geklärt.
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
13.12.2022 11:57:53
onur
Das wäre (statt der 7):
=SUMMENPRODUKT((Tabelle2!Z1S1:Z100S100=Z3S2)*ZEILE(Z1:Z100))/ZÄHLENWENN(Tabelle2!Z1S1:Z100S100;Z3S2)
Langsam solltest du aber mit Hilfszellen arbeiten, denn die Formel wird immer länger.
AW: Verknüpfung Tabellenblatt über Zelle
13.12.2022 16:14:44
Susanne
Onur, das ergibt zwar die Sieben bei mir (sofern ich wieder auf Z1S1 umschalte), aber ich kann das nicht in meine bestehende Formel erfolgreich einbauen.
Zudem da ja sowieso wieder "Tabelle2!" darin vorkommt, was ja ersetzt werden soll.
Also ich möchte die richtigen zwei Zellen komplett flexibel ermitteln.
Anbei nochmal die Datei mit Infos und etwas ausgemistet.
https://www.herber.de/bbs/user/156703.xlsx
Anzeige
AW: Verknüpfung Tabellenblatt über Zelle
13.12.2022 16:51:39
Susanne
Danke für die Datei Onur. Wenn du die Zelle mit der Tabelle auf die Tabelle 3 oder 4 änderst, kommst du dann auch noch auf Werte?
Gegen Hilfszellen habe ich absolut nichts, verwende ich gerne.
AW: Verknüpfung Tabellenblatt über Zelle
13.12.2022 17:50:27
Susanne
Werner hat mir folgende Formel vorgeschlagen:

=INDEX(INDIREKT(B$4&"A:Z");AGGREGAT(15;6;ZEILE($E$1:$E$50)/(INDIREKT(B$4&"A1:Z55")=B$5);1);  AGGREGAT(14;6;SPALTE($A$9:$Z$9)/(INDIREKT(B$4&"A1:Z55")=B$3);ZEILE(D1)))
diese in D9 funktioniert wunderbar!
AW: Verknüpfung Tabellenblatt über Zelle
13.12.2022 17:52:54
onur
Werner ist halt ein Formel-Crack - dagegen kann ich nicht anstinken. :)
Ich bevorzuge VBA.
AW: Verknüpfung Tabellenblatt über Zelle
13.12.2022 18:38:15
Susanne
Dann weiß ich ja, auf wen ich dann bei meinen ersten VBA-Gehversuchen hoffen kann :)
Dennoch, nochmal herzlichsten Dank für deine Unterstützung - das hätte bei mir ewig gedauert bis nie funktioniert!
LG, Su
Gerne !
13.12.2022 18:39:04
onur
AW: Verknüpfung Tabellenblatt über Zelle
09.12.2022 09:56:56
Steffen
Um in Ihrer Formel auf ein anderes Tabellenblatt zuzugreifen, können Sie die INDIREKT()-Funktion verwenden. Die Syntax der Funktion lautet wie folgt:

=INDIREKT(zellbezug)
In Ihrem Fall können Sie die Funktion wie folgt verwenden:


=INDIREKT(B6&"A1:F30")
Diese Formel greift auf das Tabellenblatt, das in der Zelle B6 angegeben ist, und auf den Bereich A1:F30 dieses Tabellenblatts zu. Sie können die Formel anschließend in Ihre andere Formel einsetzen.
Beachten Sie, dass Sie in der Formel ein &-Zeichen verwenden müssen, um den Tabellennamen und den Zellbereich zu verketten. Wenn Sie stattdessen ein +-Zeichen verwenden, wird der Tabellenname und der Zellbereich als separate Argumente interpretiert und die Formel wird nicht wie erwartet funktionieren.
AW: Verknüpfung Tabellenblatt über Zelle
12.12.2022 13:21:33
Susanne
Hallo Steffen, vielen Dank, dass du dich auch noch an der Problemlösung beteiligst.
Ich habe es direkt mit deinem Vorschlag versucht, aber da kommt ein #WERT-Fehler raus.
Das ist die Formel: =SUMMENPRODUKT((INDIREKT(B4&"A1:M15")=B5)*(INDIREKT(B4&"A1:M15")))
Anbei die Datei etwas aufgebaut und mit Hinweisen ausgestaltet https://www.herber.de/bbs/user/156664.xlsx
AW: hierzu ...
12.12.2022 14:35:16
neopa
Hallo Susanne,
... die Zelladresse der "letzte Zelle" in Deiner "item"-Zelle kannst Du in Deiner XL-Version mit folgender Formel ermitteln:
=ADRESSE(AGGREGAT(15;6;ZEILE($E$1:$E$50)/(INDIREKT(B4&"A1:Z55")=B5);1)+1;AGGREGAT(14;6;SPALTE($A$9:$Z$9)/(INDIREKT(B4&"A1:Z55")=B3);1))
Doch wozu braucht Du die Zell-Adresse? Eine solche benötigt man in den seltensten Falle.
Z.B.. kannst Du in Deiner Beispieldatei die Summe der letzten Jahreswerte dynamisch einfach wie folgt ermitteln: =SUMME(INDEX(INDIREKT(B4&"A:Z");;AGGREGAT(14;6;SPALTE($A$9:$Z$9)/(INDIREKT(B4&"A1:Z55")=B3);1)))
Gruß Werner
.. , - ...
AW: hierzu ...
12.12.2022 17:05:53
Susanne
Hallo Werner,
auch dir vielen Dank, dass du dich an meiner Herausforderung beteiligst!
Bei deiner Formel zur Adresse bringe ich $I$9 heraus, wenn ich auf Tabelle2 suche. Aber statt dem "+1" einfach "+0" oder ganz weglassen bringt mich zu einer richtigen Zelle in der korrekten Zeile. Sprich, die $$ und Spalte ziehe ich dann noch ab.
Ich möchte die Zeile deshalb ermitteln, weil ich alles schon flexibel aufgebaut habe, bis auf die Zeile mit "Item" und der Suche nach den letzten beiden Perioden für die Sales. Den letzten Wert kann ich nicht einfach verwenden, weil danach Units usw. der letzten Jahre folgen. Deshalb suche ich auch nach "sales value eur" und dann der Sprung in die darunterliegende Zeile.
Schaffen wir das auch noch? :)
LG, Su
AW: nun ...
12.12.2022 17:52:19
neopa
Hallo Susanne,
... die von mir ermittelte Zell-Adresse war bereits die des ersten Wertes unterhalb des letzten Eintrages der "item" Zelle. Wenn Du in der Formel die +1 weg lässt, erhältst Du natürlich in Deiner Datei stets die für "Oct 21-Sep 22".
Die vorletzte Zelladresse erhältst Du einfach indem Du in der Formel ...Z55")=B3);1))) einfach aus der 1 eine 2 machst (damit wird der 2. größte Spaltenwert für die Suchkriterien ermittelt; bei einer 4 die 4. größte ...)
Noch immer hast Du aber noch nicht angegeben, wozu Du die Zelladressen wirklich brauchst. Denn wie ich bereits schrieb, kommt man meist einfacher ohne ADRESSE() zum gewünschten Endergebnis.
Gruß Werner
.. , - ...
AW: nun ...
13.12.2022 10:55:22
Susanne
Hallo Werner,
das habe ich bemerkt. Ich finde die Formel auch super leicht und würde sie genau so von dir übernehmen, wenn da nicht ein kleines Aber wäre, dass in der Tabelle noch viel mehr Werte und leider auch mehrere Zellen ident sind und "EUR Oct 21-Sep 22" beinhalten.
Anbei nochmal die Datei mit etwas mehr Werten.
https://www.herber.de/bbs/user/156689.xlsx
Früher meinte ich auch, dass ich leicht das "Z7" ersetzen kann, aber selbst da hänge ich nun. Das ist der letzte fixe Bezug auf meinem Tabellenblatt. Wie bringe ich den raus? Ich wollte ursprünglich von dem Adressergebnis von "Item" (in Zeile 8 oder 9, je nach Tabellenblatt) ausgehen. 

=INDIREKT($B$4&ADRESSE(D8;VERGLEICH($B$3;INDIREKT($B$4&"Z7";FALSCH);1)))

AW: offensichtlich hast Du übersehen
13.12.2022 11:27:58
neopa
Hallo Susanne,
... dass meine Formel in D16 auch für Deine geänderten Datenbeispiel die richtige Zelladresse ermittelt. Das kannst Du leicht überprüfen, wenn Du anstelle ADRESSE() in der Formel INDEX(INDIREKT(...);...) einsetzt, womit der Ergebniswert der jeweiligen Tabelle ermittelt wird.
Also: =INDEX(INDIREKT(B4&"A:Z");AGGREGAT(15;6;ZEILE($E$1:$E$50)/(INDIREKT(B4&"A1:Z55")=B5);1)+1;AGGREGAT(14;6;SPALTE($A$9:$Z$9)/(INDIREKT(B4&"A1:Z55")=B3);1))
Gruß Werner
.. , - ...
AW: offensichtlich hast Du übersehen
13.12.2022 16:21:25
Susanne
Werner, das ist nett gemeint, aber ich brauche nicht die Summe, sondern wirklich nur die beiden Zellen mit den Namen "Oct 21-Sep 22" und "Oct 20-Sep 21".
Also immer die zwei von Rechts mit flexiblen Bezug (ohne Tabelle2! und ohne Spalte 7 etc). Weil sich das halbjährlich ändert.
Mein letzter, fast funktionierender Stand lautet: 

=INDIREKT($B$4&ADRESSE(D8;VERGLEICH($B$3;INDIREKT($B$4&"Z7";FALSCH);1)))
Nur leider wird hier die Z7 aufgeführt, was mir ja auf den anderen Tabellenblättern wieder nichts hilft.
Anbei nochmal die Datei mit Informationen und etwas aufgeräumter.
https://www.herber.de/bbs/user/156703.xlsx
Kann ich dich mit mehr Infos untestützen?
AW: meine Formel ist variabel anpassbar ...
13.12.2022 16:41:05
neopa
Hallo Susanne,
... und sie braucht auch nicht der Hilfszelle in D8. Für die von Dir angestrebten Datenwerte, kannst Du mit dieser wie folgt ermitteln.
=INDEX(INDIREKT(B$4&"A:Z");AGGREGAT(15;6;ZEILE($E$1:$E$50)/(INDIREKT(B$4&"A1:Z55")=B$5);1); AGGREGAT(14;6;SPALTE($A$9:$Z$9)/(INDIREKT(B$4&"A1:Z55")=B$3);ZEILE(D1)))
Diese Formel in D9 und dann einfach noch nach D10 kopieren.
Die dafür gegenüber der vorherigen Formel vorgenommen Anpassung hab ich fett markiert und noch +1 gelöscht.
Gruß Werner
.. , - ...
AW: meine Formel ist variabel anpassbar ...
13.12.2022 17:18:56
Susanne
Werner, das klappt super! Herzlichsten Dank!
Das mit der Aggregats-Formel finde ich genial! Was hat es mit ZEILE($E$1:$E$50) und ZEILE(D1) auf sich?
AW: bitteschön und ...
13.12.2022 17:56:13
neopa
Hallo Susanne,
.... zu Deinen Zusatzfragen:
- ZEILE(D1) ergibt als Formel das Ergebnis 1, demzufolge, wenn dieser Formelteil nach unten kopiert wird ZEILE(D2) und dies 2, dann 3 ... ist als ein Zählwert der als 4. Argument für die Funktion benötigt wird. Ich hätte auch z.B. ZEILE(N1) einsetzen können. Wichtig ist nur, dass keine Spaltenbezeichnung genutzt wird, deren Spalte im Tabellenblatt aus anderen Gründen mal gelöscht werden könnte.
- ZEILE($E$1:$E$50) als Teilformel ergibt demzufolge eine Zahlenmatrix beginnend mit 1;2;3 ... und endend mit 49;50
Diese Matrix hab ich so definiert, weil ich davon ausgegangen bin, dass in Deinen Datentabellen die entsprechenden Suchwerte in den ersten 50 Zeilen zu finden sein sollten. Kannst Du also noch anpassen.
Gruß Werner
.. , - ...
BESTEN DANK
13.12.2022 18:35:02
Susanne
Vielen Dank für die Erläuterung!
LG, Su
AW: bitteschön, gerne owT
13.12.2022 19:05:18
neopa
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige