MS-Office-Forum

Zurück   MS-Office-Forum > Microsoft Office > Microsoft Excel
Registrieren Forum Hilfe Alle Foren als gelesen markieren

Banner und Co.

Antworten
Ads
Themen-Optionen Ansicht
Alt 15.05.2019, 19:55   #1
PatrickKr
Neuer Benutzer
Neuer Benutzer
Standard Formel - 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
PatrickKr ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2019, 20:16   #2
Der Steuerfuzzi
MOF Profi
MOF Profi
Standard

Hallo,

Zitat: von PatrickKr Beitrag anzeigen

Ü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.

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

__________________

Gruß
Michael
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2019, 20:17   #3
MisterBurns
MOF Meister
MOF Meister
Standard

Hallo Patrick,

das geht zB so:
Code:

Sub test()
Dim i As Integer
i = Sheets.Count
Sheets(i).Range("D5").FormulaR1C1 = "=Tabelle" & i - 1 & "!RC-R[42]C[2]"
End Sub

__________________

Schöne Grüße
Berni
MisterBurns ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2019, 20:28   #4
Der Steuerfuzzi
MOF Profi
MOF Profi
Standard

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

Function VorherigesBlatt(Bezug As Range) As Range
    Set VorherigesBlatt = Worksheets(Application.Caller.Parent.Index - 1).Range(Bezug.Address)
End Function
Verwendung:
=VorherigesBlatt(D5)-F47

__________________

Gruß
Michael
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2019, 21:45   #5
MisterBurns
MOF Meister
MOF Meister
Standard

@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.

__________________

Schöne Grüße
Berni
MisterBurns ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 15.05.2019, 21:51   #6
Der Steuerfuzzi
MOF Profi
MOF Profi
Standard

@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.

__________________

Gruß
Michael
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2019, 03:49   #7
lupo1
MOF Koryphäe
MOF Koryphäe
Standard

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

Geändert von lupo1 (16.05.2019 um 04:18 Uhr).
lupo1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2019, 06:56   #8
MisterBurns
MOF Meister
MOF Meister
Standard

Hi Michael,

gutes Argument

__________________

Schöne Grüße
Berni
MisterBurns ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2019, 07:09   #9
Der Steuerfuzzi
MOF Profi
MOF Profi
Standard

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

Zitat: von lupo1 Beitrag anzeigen

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.

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.

__________________

Gruß
Michael
Der Steuerfuzzi ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2019, 08:29   #10
lupo1
MOF Koryphäe
MOF Koryphäe
Standard

Deshalb ja auch das Wort "irrelevant", steuerfuzzi.

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.
lupo1 ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2019, 14:11   #11
PatrickKr
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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
PatrickKr ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 16.05.2019, 14:43   #12
MisterBurns
MOF Meister
MOF Meister
Standard

Zitat:

RC-R[42]C[2]
verstehe ich aber überhaupt nicht. Könntest du mir das erläutern?

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.

__________________

Schöne Grüße
Berni
MisterBurns ist gerade online  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 07.06.2019, 11:28   #13
PatrickKr
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

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=)
PatrickKr ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Alt 12.06.2019, 11:50   #14
PatrickKr
Threadstarter Threadstarter
Neuer Benutzer
Neuer Benutzer
Standard

Problem gelöst.

Private Sub Worksheet_Activate()
Dim x As Double, I As Double
Range("A2350").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
PatrickKr ist offline  
verlinken auf Del.icio.us Diese Seite zu Mister Wong hinzufügen
Antworten Auf Beitrag antworten
Ads
Antworten


Aktive Benutzer in diesem Thema: 1 (Registrierte Benutzer: 0, Besucher: 1)
 
Themen-Optionen
Ansicht

Forumregeln
Es ist Ihnen nicht erlaubt, neue Themen zu verfassen.
Es ist Ihnen nicht erlaubt, auf Beiträge zu antworten.
Es ist Ihnen nicht erlaubt, Anhänge anzufügen.
Es ist Ihnen nicht erlaubt, Ihre Beiträge zu bearbeiten.

vB Code ist An.
Smileys sind An.
[IMG] Code ist An.
HTML-Code ist An.
Gehe zu


Alle Zeitangaben in WEZ +1. Es ist jetzt 20:42 Uhr.



Powered by: vBulletin Version 3.6.2 (Deutsch)
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.

Copyright ©2000-2018 MS-Office-Forum. Alle Rechte vorbehalten.
Copyright ©Design: Manuela Kulpa ©Rechte: Günter Kramer
Eine Verwendung der Inhalte in anderen Publikationen, auch auszugsweise,
ist ohne ausdrückliche Zustimmung der Autoren nicht gestattet.