Office: Zellbezug auf vorheriges Tabellenblatt

Hallo liebe Excelprofis,

Ich habe jetzt viele viele Stunden damit zugebracht eine Lösung für mein Problem zu finden, sowohl über Formeln als auch über VBA. Leider hat es nichts ergeben und ich muss mir Hilfe suchen.

Ich habe eine Datei in der ich via Makro Tabellenbläter kopiere und ans Ende stelle. Das funktioniert auch so wie ich das möchte.

Nun habe ich Im Feld D5 die folgende Formel:
='Tabelle (1)'!D5-F47
Nur soll sich die Formel nicht auf Tabelle 1 beziehen, sondern auf das jeweils vorherige Tabellenblatt.

Über das Makro habe ich es geschafft den Wert den ich haben möchte zu erreichen. Wenn sich aber nun nachträglich doch nochmal etwas im vorherigen Blatt ändert aktualisiert sich der Wert natürlich nicht.


Ich bin sehr gespannt auf eure Lösungen. Wenn ihr die Muße habt sehr gerne auch mit viel Erläuterungen, damit ich auch verstehe wie die Lösung funktioniert auf die ich nicht gekommen bin.

LG Patrick

:)

 

Hallo,

Warum zeigst Du das Makro denn nicht? Hast Du eine benutzerdefinierte Funktion geschrieben?

 

Hallo Patrick,

das geht zB so:
Code:

 

Ich würde das mit einer UDF machen:
Code:

Verwendung:
=VorherigesBlatt(D5)-F47

 

\@Michael: Wozu? Wenn das neue Tabellenblatt schon per Makro erstellt wird, wieso dann die Formel dort händisch eintragen? Der Sinn erschließt sich mir nicht.

 

\@Berni:
Es gab zwei Gründe aus den Vorgaben des OP:
1. Er kopiert die Tabellenblätter, also die UDF würde er mitkopieren
2. Er will immer den aktuellen Wert der Tabelle davor. Wenn umsortiert wird, stimmt Deine Formel nicht mehr
Ob und inwieweit das tatsächlich relevant ist, weiß ich natürlich nicht.

 

Tabelle2!D5: =prevD5-F47 mit definiertem Namen:

prevD5: =INDIREKT(INDEX(ARBEITSMAPPE.ZUORDNEN(1);DATEI.ZUORDNEN(87)-1)&"!D5")

http://excelformeln.de/formeln.html?welcher=268

Anmerkung:
1) Hier wird - anders als im Link! - darauf verzichtet, dass ein Tabellenname auch Leerzeichen aufweisen könnte, der dann aber extra mit ' umrankt werden müsste. Soll dem Anwender seine bescheuerte Art, zu benennen, doch um die Ohren fliegen! Menno.
2) Auch wird nicht umständlich der Dateiname extrahiert. Warum auch?

ARBEITSMAPPE.ZUORDNEN(1): Horizontalarray der [Datei]!Tabellennamen der Datei
DATEI.ZUORDNEN(87): Von-Links-Position der Tabelle in der Datei, die diese Funktion verwendet

___________________________
Nicht gefragter Exkurs: Den letzten Eintrag aus Spalte A der Vorgängertabelle in der Datei erhältst Du dann über Verwendung von =LastPrevAA wie folgt:

LastPrevAA: =VERWEIS(2;1/(
INDIREKT(INDEX(ARBEITSMAPPE.ZUORDNEN(1);DATEI.ZUORDNEN(87)-1)&"!A:A")"");
INDIREKT(INDEX(ARBEITSMAPPE.ZUORDNEN(1);DATEI.ZUORDNEN(87)-1)&"!A:A"))

Zwar kann man die gleichen Formelteile INDIREKT(...) zwecks Abkürzens des Terms noch zwischenbenennen, aber bringen tut das weder für die Dateigröße noch für eine Rechenminimierung etwas.

Anmerkung 2: Die Verwendung von Excel4-Makrofunktionen ggü VBA hat 3, davon 2 heute irrelevante, Vorteile:
a) in uralten Excels/Windows-Kombis gibt es keinen Warnhinweis wegen Excel4-Makrofunktionen, aber immer schon für VBA
b) lässt sich eine Excel4-Makrofunktion auch in Excel4 (statt erst ab Excel5, wo VBA eingeführt wurde) verwenden. Daher ja auch der Name.
c) rechnet schneller als VBA

 

Hi Michael,

gutes Argument *Smilie

 

\@Lupo: Ich kann leider bei Deinen drei aufgeführten Vorteilen keinen wirklich relevanten erkennen. Aber der ist gut:

Ich weiß ja nicht wieviele hier noch ein Excel vor Version 5 hier im Einsatz haben. Zumindest produktiv dürfte sich das in sehr sehr sehr engen Grenzen halten.

 

Deshalb ja auch das Wort "irrelevant", steuerfuzzi.*cool.gif*

Ansonsten erschien Excel4-M immer irgendwie "eingebauter" als VBA. Noch ein - ebenso irrelevanter - Vorteil:
d) Die Datei sollte sich in SpreadCE verwenden lassen. SpreadCE läuft sogar auf dem Smartphone (unter Android). Allerdings ist es für die tatsächliche Arbeit leider nicht stabil genug.
Zu a): Excel 2000 unter Win XP könnte die meldungslose Verwendung von Excel4-Makrofunktionen noch zulassen. Ich teste es nachher mal auf meinem 1-GB-Netbook mit 12 GB RAM, der immer noch damit läuft.

Das mit Excel4-Makro muss man den jüngeren halt im Sinne eines Geschichtsunterrichts einmal gesagt haben. Excel 1 bis 3 hatten noch keine Programmier-Sprache. Unter Windows gab es außerdem kein Excel 1, nur beim Mac.

 

Hallo zusammen,

erst einmal vielen Dank für die vielen Antworten.
Ich bin leider erst wieder nächste Woche im Büro um es dann an der Tabelle auszuprobieren. Bin aber schon gespannt.

@Steuerfuzzi:
Ich habe das Makro nciht mit aufgelistet, da ich gar keines habe. Habe es über verschiedene Wege versucht und immer wieder verworfen. Nach dem letzten verwerfen kam nichts mehr heraus, dass ansatzweise funktioniert hat, daher habe ich nichts...

Den Ansatz mit UDF finde ich schonmal gut. Das hatte ich noch garnicht ausprobiert. Ich war zwischenzeitlich bei Makros, die immer dann den Wert übertragen wenn das Tabellenblatt gewechselt wird. Oder aber das Makro, dass das neue Tabellenblatt erstellt sollte es entsprechend mit eintragen. Mit meien Fähigkeiten beides Sackgassen.


@Mister Burns. Ich glaube ich verstehe in etwa was diese Sub tun soll. Werde ich mal testen.
RC-R[42]C[2]
verstehe ich aber überhaupt nicht. Könntest du mir das erläutern?
Umsortiert wird nicht, daher ist der Einwurf von Steuerfuzzi irreleavant.


@lupo: Das ist mir definitiv zu hoch, ich habe im Grunde nichts verstanden. Ich werde erstmal die beiden anderen Lösungsvorschläge probieren.


Vielen Dank nochmal an euch alle. Ich melde mich dann nächste Woche nochmal.

LG

 

RC steht für Rows und Columns und sagt, wieviele Zeilen und Spalten von der Ausgangszelle hinzugezählt werden sollen.
Du schreibst die Formel ja in Zelle D5. Damit bedeutet "RC" soviel wie R0C0, also: gehe von D5 null Zeilen nach unten und null Spalten nach rechts.

R[42]C[2] heißt dann wiederum: gehe von D5 42 Zeilen nach unten und 2 Spalten nach rechts. Damit wird D5 zu F47.

 

Hallo Excelprofis,


nun bin ich endlich dazu gekommen es auszuprobieren.
Kam leider vieles viele sanderes dazwischen.

Ich habe die UDF von Steuerfuzzi genutzt. Hat Super funktioniert.


Vielen Dank!!


Jetzt habe ich das nächste Problem.

Auf einem anderen Tabellenblatt sollen alle Tabellennamen und diverse WErte angegeben werden.

Dazu habe ich folgende Sub erarbeitet/erklaut.

Private Sub Worksheet_Activate()
Dim x As Double, i As Double
x = 23
For i = 5 To Sheets.Count
Cells(x, 1) = Sheets(i).Name
Cells(x, 2) = Sheets(i).Cells(4, 5).Value
x = x + 1
Next i
End Sub


Name Funktioniert Problemlos. Allerdings gibt er bei Value auf dem angegeben Feld ncihts aus. Wenn ich ein anderes Feld auswähle klappts.

Die Felder die Funktionieren enthalten Text. Mein gewünschtes Feld enthält die von Steuerfuzzi vorgeschlagene Formel.


Macht Value nciht das was ich denke? Oder Macht es nciht das was ich soll?


Freue mcih auf eure Vorschläge=)

 

Problem gelöst.

Private Sub Worksheet_Activate()
Dim x As Double, I As Double
Range("A23*biggrin.gif*50").Select
Selection.ClearContents
x = 23
For I = 5 To Sheets.Count
Cells(x, 1) = Sheets(I).Name
Cells(x, 2) = Sheets(I).Range("F47")
Cells(x, 3) = Sheets(I).Range("D5")
Cells(x, 4) = Sheets(I).Range("F49")
x = x + 1
Next I
End Sub



Range stattValue war des Rätsels Lösung