Frage von Geradon, 56

EXCEL: Wert aus vorherigem Tabellenblatt kopieren?

Hey,

ich brauche eine Formel, um den Wert von L7 des vorherigen Tabellenblatts zu kopieren. Ich habe bis jetzt nur Formeln gefunden, bei denen ich sagen muss "Kopiere Tabellenblatt L7 aus Tabellenblatt XY". Ich brauche aber eine Formel, die sagt "Kopiere Tabellenblatt L7 aus vorherigem Tabellenblatt", unabhängig davon, wie das vorherige Tabellenblatt heißt.

Expertenantwort
von Iamiam, Community-Experte für Excel, 20

Es geht mit einer ertwas länglichen Formel (erst zuletzt(!) in L7 oder sonstwo einfügen!):

L7: =INDIREKT(INDEX(Indexliste;VERGLEICH(TEIL(ZELLE("Dateiname");FINDEN("]";ZELLE("Dateiname");1)+1;999);Vergleichsliste;0))&"!"&ZELLE("Adresse";L7))

Allerdings musst Du dazu (am besten vorher) eine Liste Deiner Tabellennamen erstellen. Und umordnen darfst Du die dann nicht mehr, höchstens davor oder danach Blätter einfügen.

Die Liste geht einfach, wenn Du fortlaufende Monate hast: Schreibe in

F4: =TEXT("1."&ZEILE(A1)&".2016";"MMMM")

und ziehe das runter: Du hast jetzt eine Text-Liste von Januar bis Dezember. Darüber schreibst Du in

F3: =F15

Die Monatsliste geht jetzt also von F3=Dezember bis F15=Dezember.

Könntest auch Jan, Feb, ...erzeugen mit dem Format "MMM" in F4

markieren nun F4:F15 und benenne es mit Vergleichsliste (das einfach ins Adressfeld links neben der Bearbeitungszeile schreiben.

Dasselbe mit F5:F15 , benennen als Indexliste

Die beiden Bereiche dienen der problemlosen Kopierbarkeit auf andere Blätter, Du brauchst so die Listen nur einmal und Du musst beim Kopieren nichts extra anpassen.

Jetzt die obige Hauptformel einfügen 

Du kannst nun noch die Bereiche verschieben (Gesamtbereich Indexliste samt eingeschlossenem Vergleichsliste so wie es passt.)

Die Formel kannst du beliebig kopieren, das letzte Zelle("ADRESSE";L7) passt sich an bzw kannst Du beliebig ändern.

Die beiden Bereiche brauchst Du deshalb unterschiedlich, damit bei Vergleich im Monat Dezember auch der untere Dezember lokalisiert wird und nicht der obere, aber bei Januar deshalb kein fehlender Dezember darüber stört.

Kommst Du klar?

Das Ganze ginge auch mit einer Liste beliebiger Blattnamen, die wäre nur etwas aufwändiger zu erstellen.

Kommentar von Iamiam ,

mir ist noch aufgefallen: könnte sein, dass xl auch bei Dir eine Weile braucht, um die akruelle Cursorposition zu verarbeiten, wenn Du aus einem anderen Blatt kommst. Sicherheitshalber einfach nochmal F9 drücken, auch wenns vllt nervt...

Ausserdem: die Methode ist doch unempfindlich gegen das Umordnen der Blätter, es wird immer auf das Blatt in der Liste verwiesen, das drüber steht!, Auch dAS KÖNNTE MAN UMODRNEN1

Kommentar von Iamiam ,

aber die Formel im Link von asta ist Spitze, da fehlt nur die Zeile Application.Volatile, damit die Makroformel auch garantiert auf F9 reagiert. Kann man auch 2 Blätter zurück oder ein Blatt nach vorne einstellen! (ich muss mir das Prinzip erst selbst nochmal zu Gemüte führen, aber es funktioniert einwandfrei! Wenn man sowas sieht, merkt man, dass wir hier alle Dilettanten sind, aber auch, was man aus dem Netz alles rausholen kann! Bei aller Arbeit, die ich selbst und vllt auch andere da reingesteckt haben: den Stern hat asta verdient!)

Diese Makroformel reagiert übrigens auf Umordnen nach F9: Vorblatt ist immer das Blatt links daneben, das wird dann eben aktualisiert!

Expertenantwort
von Suboptimierer, Community-Experte für Excel, 29

Am einfachsten geht das per VBA, wenn deine Tabellenblätter nicht nach Schema benannt worden sind. 

http://www.office-loesung.de/ftopic288078\_0\_0\_asc.php

Sind sie nach Schema benannt worden (Bsp. Tabelle1 bis Tabelle10), dann musst du nur den Index des aktuellen Tabellenblatts herausfinden.

=TEIL(ZELLE("dateiname");FINDEN("Tabelle";ZELLE("dateiname"))+LÄNGE("Tabelle");2^16)

Sagen wir, das steht in A1. Davon kannst du 1 abziehen und mit INDIREKT referenzieren.

=INDIREKT("Tabelle"&A1-1&"!C3")

Damit referenzierst du auf dem vorherigen Tabellenblatt die Zelle C3.

Für die erste Formel kannst du dir einen Namen anlegen, sodass dir z. B. der Name VorherigeTabelle direkt TabelleX! ausgibt.

Kommentar von Geradon ,

Mit VBA habe ich noch nie gearbeitet. Geht das nicht per einfacher Formel? Die Tabellen sind Januar, Februar, .... benannt. 

Kommentar von Ninombre ,

Das ist keine normale Excel Logik, daher gibt es keine einfache Formel für die Reihenfolge der Tabellen. Vermutlich sind 11 manuelle Klicks schneller als Excel die Reihenfolge der Monatsnamen beizubringen 

Kommentar von Suboptimierer ,

Das geht höchstens mit verketteter Liste.

Leg dir auf ein Tabellenblatt, das du später verstecken kannst, einen benamten Bereich Monate an. Schreib dafür in A1 "Januar" und zieh am Kopierkreuz bis Zeile 12.

Dann legst du 3 Namen im Namensmanager an (Formeln → Definierte Namen → Namensmanager)

AktTab  =TEIL(ZELLE("dateiname");FINDEN("]";ZELLE("dateiname"))+1;2^16)
Monate  =Hilfsblatt!$A$1:$A$12
TabPrev =WENN(AktTab="Januar";"Dezember";INDEX(Monate;VERGLEICH(AktTab;Monate;0))))

Mit TabPrev erhältst du den Namen des Vormonats, den du mit INDIREKT verwenden kannst.

Kommentar von Iamiam ,

Das mit dem Namen für vorherige Tabelle muss ich mir merken!

Man muss nur diesen Benannten Bereich immer mitschleppen und ich hab den Verdacht, dass man den auch nur einmal verwenden kann nund nicht fortlaufend von Blatt zu Blatt  wandern, um die jeweilige Vor-Zelle zu übernehmen. Mag das aber heute nicht mehr durchdenken!

Kommentar von Iamiam ,

schau Dir mal die Makroformel im Link von asta an! Bin begeistert!

Kommentar von Suboptimierer ,

Stimmt, grob überflogen und sieht gut aus.

Auch wird dort eine VBA-Alternative geboten. Mittels VBA ist das Vorgängerblatt viel einfacher zu ermitteln und ohne eine Namensstruktur vorauszusetzen, als mit Formel, weil die Tabellenblätter durchindiziert sind.

Antwort
von Ghanasoccer, 21

Benennst du deine Blätter nach einer Reihenfolge, wie zB Jan, Feb, Mar, Apr, ... oder 1, 2, 3, 4, 5, 6, ... oder Person1, Person 2, Person 3, ...

Falls Ja, kannst du eine Formel benutzen!

Ein erster Teil: =TEIL(ZELLE("Dateiname";A1);FINDEN("]";ZELLE("Dateiname";A1))+1;59)

Antwort
von DerEinsiedler, 31

Das ist nicht trivial zu lösen meines Wissens nach...

Welches Problem willst Du denn damit lösen? Eventuell gibt es andere Lösungen dafür.

Kommentar von Iamiam ,

asta hat einen Spitze-Link!

Antwort
von newcomer, 34

tippe auf die Zielzelle und schreibe = dann gehste auf anderes Tabellenblatt und drückst mit Maus auf die Quellzelle. Schon steht in Zielzelle die komplette Formel

Kommentar von DerEinsiedler ,

... das ist nicht, was der OP wollte. So bekommt er nur einen harten Verweis auf das Blatt.

Kommentar von Iamiam ,

"die komplette Formel" steht nicht drin, aber der Wert der QuellZelle

hab jetzt fast 2 h an einer Formel gebastelt und das auch geschafft, aber in der Zeit hätte man viiieeele harte Verweise machen können! Auch das Implementieren meines Formelsystems kostet Zeit und wenn es nicht zuviele Verweise wären bzw die Kontrolle der Verweise schwierig wäre dann sollte man tatsächlich einfach hart übernehmen!

Antwort
von asta311, 32

Das ist kompliziert, am besten mit Makro.

Kommentar von Geradon ,

Mit Makros kenne ich mich leider gar nicht aus :(

Kommentar von Iamiam ,

Der Link ist gut! dafür DH!

Kommentar von Iamiam ,
Die VBA-Funktion aus dem Link funktioniert auf Anhieb

und einfach.

Lediglich sollte nach der Zeile Function...  noch eine Zeile mit

Application.volatile

rein, dann spricht die Funktion auf F9 an und aktualisiert sich, SPITZE!

Keine passende Antwort gefunden?

Fragen Sie die Community