[Excel] bezug auf voriges Blatt in der selben Datei

iUser · 17. April 2012

Hi!

ich hoffe ihr könnt mir bei meinem problem helfen.
Folgende Problemstellung: meine drei kollegen und ich sollen uns in einer Excel-Tabelle für unseren Einstzplan nach tagen und stunden eintragen. Alle Blätter sind in der selben datei, die einzelenen Blätter haben stets den namen "KW X-20YZ" (zum beispiel KW 7-2012) da meine Kollegen und ich aber mit kalenderwochen wenig anfangen können habe ich nun vor, statt einfach nur "Montag", "Dienstag", ... "Freitag" in der ersten zeile stehen zu haben über die datumsfunktionen von excel den tag und das datum einzufügen. das sieht dann für KW 8 bspw. so aus:

B1='KW 7-2012'!B1+7

wenn ich jetzt aber um eine Kalenderwoche weiter gehe steht dann aber immer noch
'KW 7-2012'!B1+7
in der Zelle, es sollte aber
'KW 8-2012'!B1+7
sein. da wir immer das letzte Blatt kopieren und anfügen, sollte es also so sein, dass ich in einer wie auch immer gearteten Form bezug nehmen kann auf das vorige blatt, dass also auch KW 23-2012 auf das Blatt KW 22-2012 bezogen wird.

Was mir google ausspuckt ist alles andere als verständlich, ich hoffe ihr könnt mir helfen.

Herzlichen dank im vorraus

diablobase · 17. April 2012

ohne vba ist das soweit ich weiß nicht möglich, mit vba allerdings sehr leicht

Öffne den VBA-Editor mit ALT+F11 und füge über das Menü ein Modul ein.
In das Modul fügst du folgende Funktion ein:

Code:

Public Function lastSheet() As String
    
    Dim ws As Worksheet
    
    Set ws = Worksheets(ActiveSheet.Index - 1)
    
    lastSheet = ws.Name
    
End Function

Diese Funktion kannst du jetzt als ganz normale Formel (=lastSheet()) in den Zellen verwenden und gibt dir jedesmal den Namen des Registerblattes davor zurück.

Die Funktion ist sehr einfach und fängt keine Fehler oder sonstwas ab.

Mogwai · 17. April 2012

Wenn ich Dich richtig verstehe, musst Du einfach das Zellenformat ändern auf Datum. Du erfasst im ersten Tabellenblatt den Montag als Datum (16.4.2012) und formatierst die Zelle dann so, dass nur der Wochentag angezeigt wird (einfach alles löschen hinter TTTT). Dann sollte die Formel funktionieren.

Mogwai

iUser · 17. April 2012

das ist bereits alles erledigt.
das was ich möchte, ist über blätter hinweg kontinuiertlich das datum fortzuführen

Domi83 · 17. April 2012

Sein Problem ist, dass er mit der Drag & Drop Methode die Excel so schön macht, automatisch seine Formel erweitern möchte.. aber nicht so wie in meinem Screenshot, dass kann jeder der schon mal mit Excel gearbeitet hat.

Was er machen möchte ist, von C2 - C10 runter ziehen, und Excel soll erkennen das er nicht A2 - A10 ansprechen soll, sondern "KW 7-2012'!B1+7", "KW 8-2012'!B1+7", "KW 9-2012'!B1+7" etc.

So etwas ähnliches habe ich nämlich auch. Ich möchte von C3 - C7 runter ziehen, und Excel soll das automatisch erkennen das ich aber A2, A4, A6, A8 ansprechen möchte.. doch gefunden habe ich noch nichts

ryan_blackdrago · 17. April 2012

Ganz spontan: Über zwei Hilfszellen :

In B1

Code:

=RECHTS(ZELLE("Dateiname";A1);(LÄNGE(ZELLE("Dateiname";A1))-FINDEN("]";ZELLE("Dateiname";A1);1)))

In C1:

Code:

=TEIL(B1;3;2)

Ihr erstellt ja pro KW ein neues Tabellenblatt. In B1 wird dann der Name des aktuellen Tabellenblatts übernommen. In C1 wird dann alles in die aktuelle KW aufgeteilt. Dann noch per Formatierung & Formel das Datum / Wochentag berechnen lassen

Alles in einer Hilfszelle zusammengefasst:

Code:

=TEIL((RECHTS(ZELLE("Dateiname";A1);(LÄNGE(ZELLE("Dateiname";A1))-FINDEN("]";ZELLE("Dateiname";A1);1))));3;2)

iUser · 17. April 2012

ich hab mal nen anonymisierten einsatzplan hochgeladen, es geht jeweils um die zelle B1, und auch wenn ich normalerweise mit excel normalerweise recht geschickt bin, geht das doch an meine Grenzen.

das mit dem dateinamen hat auch ein kleines problem: die datei liegt in der firma normalerweise im DMS und bekommt beim export 4 zufällige zeichen vorangestellt...

Ergänzung (17. April 2012)

Also die Lösung von diablobase klang vielversprechend, aber wie ich es auch anstelle, es geht nicht. zusätzliches Problem ist, dass das original eine 2007er datei ist, ich aber nur excel 2003 habe. ich werde nachher nocheinmal eine anonymisierte version der originaldatie hochladen, jetzt muss ich aber erst mal zur uni... bis denne

diablobase · 17. April 2012

Anhand deiner Beispieldatei habe ich die passende Lösung für dich gefunden.
Nimm die Funktion aus meinem ersten Post ganz oben (kam wegen manueller Freischaltung leider später) und benutz in Zelle B1 folgende Formel:

Code:

=INDIREKT("'" & lastsheet() & "'!B1")+7

Kurze Erklärung weil es bei dir nicht funktioniert hat:
Meine Funktion gibt dir den Namen des Registerblatts als Text zurück, die Formel "INDIREKT" wandelt dann den Text in einen gültigen Bezug um.
Da die Namen deiner Registerblätter aber Leerzeichen enthalten muss der Namen mit einfachen Anführungszeichen ( ' ) umschlossen sein damit es funktioniert.

Domi83 · 17. April 2012

Darf ich Excel-Technisch, auch mal etwas fragen..

Chef hat ungefähr 2007, eine Excel Tabelle für die Umsatz-Statistik haben. Diese führt er für sich und möchte auch weiterhin seine Tabelle führen. Also brauche ich auch keine neuen Tipps mit welchen Programmen man das machen kann etc.

Es gibt nun zwei Tabellen in dieser Excel-Mappe, einmal für das laufende Jahr und einmal der gesamte Kram zusammen gepackt in einer Gesamt Tabelle. Und da setze ich jetzt mal kurz an, das Feld "B1" nimmt Bezug auf "C36" von Tabelle 1, "B2" auf "E36" und so weiter. Es wird immer einer übersprungen und auch nach rechts versetzt, nicht nach unten.

Gibt es dafür einen Trick, wie man das automatisiert machen kann, mit der Zuordnung? Ich darf nämlich jedes mal in einem neuen Jahr alles durch klicken, damit alles funktioniert

Ich wollte ihm das schon mal ins Intranet schieben und mit MySQL aufbauen, aber das möchte er alles nicht. Das soll eine Excel Tabelle bleiben, die er pflegt

Vielleicht kann man mir da auch mal einen Tipp geben.
Gruß, Domi

diablobase · 19. April 2012

@Domi83

habe jetzt wirklich sehr lange versucht das mit formeln zu lösen, was mir aber nur teils gelungen ist...
deswegen habe ich das jetzt mit einer vba funktion gemacht, mag ich sowieso lieber

dennoch wird eine hilfszeile benötigt in welcher lediglich die erste zelle steht welche für diese spalte benötigt wird (siehe screenshot)

Code:

Public Function calc(row As Long, col As Long) As Double

    Dim ws As Worksheet
    Dim val As Variant
    Dim valSheet As String
    Dim valRow As String
    Dim valCol As String
    Dim i As Integer
    Dim s As String
    Dim result As String
    
    Set ws = ActiveSheet
    val = Split(ws.Cells(1, col).Value, "!")
    
    valSheet = val(0)
    
    For i = 1 To Len(val(1))
        s = Mid(val(1), i, 1)
        If IsNumeric(s) Then
            valRow = valRow & s
        Else
            valCol = valCol & s
        End If
    Next
    
    valCol = Range(valCol & "1").Column
    valCol = CStr(CInt(valCol) + ((row - 3) * 2))
    
    result = ActiveWorkbook.Sheets(valSheet).Cells(CInt(valRow), CInt(valCol)).Value
    calc = result

End Function

diese funktion in ein modul im vba-editor einfügen und dann kannst du diese in den zellen folgendermaßen aufrufen:

Code:

=calc(ZEILE();SPALTE())

das einzige problem ist jetzt leider die aktualisierung der werte, d.h. änderst du in der ersten tabelle einen wert werden die formeln erst bei einer erneuten bestätigung der formel aktualisiert.
ich weiß gerade leider nicht wie man das umgeht, habe aber auf der arbeit sowas schonmal gemacht und kann es bei zeiten nachreichen.

Domi83 · 19. April 2012

Moin moin.. Ich werde das morgen in der Firma einmal ausprobieren und rum spielen. Bedanke mich aber schon mal vielmals

Das man mit vba Scripts viel in Excel machen kann, habe ich schon oft gelesen und gehört. Aber davon habe ich leider gar keine Ahnung. Bin froh, dass ich meine PHP und SQL Geschichten gut hin bekomme

Gruß, Domi

diablobase · 20. April 2012

Wie versprochen der Nachtrag:

Code:

Public Function calc(row As Long, col As Long) As Double
 
    Dim ws As Worksheet
    Dim val As Variant
    Dim valSheet As String
    Dim valRow As Long
    Dim valCol As Long
    Dim result As Double
    
    Set ws = ActiveWorkbook.Sheets("gesamt")
    
    val = Split(ws.Cells(1, col).Value, "!")
    valSheet = val(0)
    valRow = Range(val(1)).row
    valCol = Range(val(1)).Column
    valCol = valCol + ((row - 3) * 2)
    
    result = ActiveWorkbook.Sheets(valSheet).Cells(valRow, valCol).Value
    calc = result
 
End Function

Ich habe dabei auch nochmal die Funktion aufgeräumt und gekürzt, spät Abends sollte ich wohl nicht mehr programmieren

Am Aufruf der Funktion ändert sich nichts, das bleibt gleich, jetzt wird die Funktion aber wie die normalen Excelformeln bei jeder Änderung aktualisiert.

Auch wenn Excel manchmal sehr nervig ist kann man damit schon tolle Sachen anstellen, vorallem dann auch mit VBA

Suche

[Excel] bezug auf voriges Blatt in der selben Datei

iUser

Cadet 4th Year

diablobase

Cadet 4th Year

Mogwai

Lieutenant

Anhänge

iUser

Cadet 4th Year

Domi83

Rear Admiral

Anhänge

ryan_blackdrago

Captain

Anhänge

iUser

Cadet 4th Year

Anhänge

diablobase

Cadet 4th Year

Domi83

Rear Admiral

Anhänge

diablobase

Cadet 4th Year

Anhänge

Domi83

Rear Admiral

diablobase

Cadet 4th Year

Ähnliche Themen