Office 2007: Werte aus meheren Sheets in einem Summary Chart auswerten

[criterion]

Hi.
Folgende Herausforderung die bisher keiner meiner Kollegen meistern konnte.
Jede Woche mache ich 1-3 Inspectionen und gebe die Daten jedesmal in eine neue Tabelle ein.Es ist immer die gleiche Tabelle nur kopiere ich sie als neues sheet ans ende.Der name des sheets ist dann immer das dementsprechende Datum an dem ich die Inspection mache.Die summe der jeweiligen spalte,insgesamt 4 habe ich am schluß der spalte ausgegeben.2 mal yes 1mal no und einmal spalte ist nicht leer.
So jetzt zum eigentlichen problem.Es soll so sein.Jedesmal wenn ich ein neues sheet nach einer inspection anlege und meine Werte eingebe sollen diese in einem sheet namens Summary in einem chart ausgegeben werden.Das chart ist in die 12 monate und die 4 spalten eingeteilt.
Die werte sollen jetzt dem Monat zugeordnet werden das dem Datum meines inspections sheets entspricht.Und da sehe ich das Problem.
Woher soll Excel/das Chart wissen das ich ein neues sheet erstellt habe und das Datum zu einem bestimmten Monat gehört.

Die werte werden immer in die gleichen spalten eingetragen,des inspections sheets.

D9-D28 In D29 steht der Wert der zusammengezählten findings
E9-E28  In E29 steht der Wert der zusammengezählten findings
G9-G28  In G9 steht der Wert der zusammengezählten findings
I9-I28  In I29 steht der Wert der zusammengezählten findings

Jedes neue sheet hat dann ein neues datum und das soll später dem monat in der summary zugeordnet werden damit ich sehen kann was sich in einem monat getan und im nächsten verändert hat

Hier meine Tabellen








Bin absoluter anfänger darum soll es so verständlich wie möglih sein.

vielen dank im voraus

lg Klaus

[Beverly]

Hi Klaus,

übertrage die Werte aus jedem Tabellenblatt per Bezugs-Formel in dein Übersichts-Blatt Summary - so wie du das anscheinend schon getan hast. Ich sehe also nicht, wo da das Problem liegt.

Bis später,
Karin

[criterion]

Hi Karin.
erstmal Danke fuer die schnelle Antwort.
Das Problem liegt darin das ich ich die Werte jedesmal per Hand in den Summary Chart eingebe

Moechte das ganze automatisieren.Wie gesagt.Sobald ich einen neuen sheet anlege,sprich den neuen per copy erstelle das zugehoerige datum und die neuen Werte eintrage sollten diese gleich in der summary dem passenden Monat zugeordnet werden.Momentan trage ich die Zahlen per Hand in die Summary ein.
Nutze keinerlei Formel,da ich garnicht weis wie man sowas macht und welche Formel/n man da nutzt.
Ob das einfach mit Formeln geht bin ich mir nicht sicher.Das Tabellenblatt ist jedesmal das gleiche,aber das Datum(auch der Name des Blattes besteht aus dem Datum)aendert sich und das muss dann dem passenden Monat zugeordnet werden.Ich kopiere zwar das Blatt und wenn da Formeln waeren diese auch mit aber die waeren wohl genau fuer dieses Blatt(sprich Datum).Das neue Blatt bekommt aber ein neues Datum und das muss natuerlich auch zum Monat passen.

lg Klaus

[Beverly]

Hi Klaus,

schreibe in die Zelle F9 =Januar!D$29 (also die Zelladresse deren Wert übernommen werden soll) und kopiere diese Formel anschließend in die Zellen G9:J9. Damit hast du die Zellen per Formel verknüpft und die Werte aus der Monatstabelle Januar werden in Summary wiedergegeben. Markiere dann F9:J9 und ziehe die Formeln 1 Zeile nach unten und ersetze dann in Zeile 10 in allen Formeln "Januar" durch "Februar". Das wiederholst du jedes mal wenn du ein neues Monatstabellenblatt anlegst.

Bis später,
Karin

[criterion]

Hallo Karin.
Wenn ich das so durchführe dann wird jedesmal der explorer geöffnet und ich muss wohl die dementsprechende Tabelle auswählen.Da ich insgesamt 6 solcher Tabellen habe mit einigen sheets wäre es eine Menge Eingabearbeit.Aber das ist das kleinere Übel.Ich muss dann praktisch für jede einzelne Zelle in dem Chart die passende Tabelle aussuchen?In einem Monat koennen bis zu 8 Tabellen vorkommen,da man einer Woche 2 Inspectionen durchgefuehrt werden.
Kann ich das eintragen?
Muss ich jedesmal wenn ich eine neue Tabelle anlege diesen Vorgang wiederholen?Sieht so aus wie du es schreibst.
Eigentlich sollte das so ablaufen das wenn ich eine neue Tabelle anlege der Chart merkt da kommt was neues mit einem Datum und das muss ich jetzt auslesen und in den entsprechenden Monat eintragen.

lg Klaus

[Beverly]

Hi,

sprichst du von mehreren Tabellenblättern ein und der selben Arbeitsmappe oder von mehreren Tabellenblättern verschiedener Arbeitsmappen?
Ein "Sheet" ist die englische Bezeichnung für "Blatt" (Tabellenblatt, Arbeitsblatt) einer Arbeitsmappe (Workbook, oder allgemein Excel-Datei).

Bis später,
Karin

[criterion]

Hallo Karin.
Schoen wieder was von dir zu lesen.
Hab leider bisher keine Loesung gefunden also bist du die einzige Hoffnung
Ja sorry ich arbeite mit der englishen version.

Also ich oeffne 1ne Arbeitmappe und darin befinden sich dann die tabellenblaetter dich ich immer wieder neu anlege.Die Arbeitsmappe wird jede Woche um 1- 2 Tabellenblaetter erweitert.Ich kopiere dann das zuletzt angelegte  Tabellenblatt dann mit copy to end als Kopie und aendere lediglich den Namen des Blattes.Sprich das Datum und oben im header auch.Dann loesche ich die alten Werte und ersetze sie durch die neuen.
Und genau dieses Anlegen der neuen Tabellenblatter und das aendern der Daten sollte spaeter in dem Chart automatisch erkannt werden und die neuen Daten dann uebernommen.Lege ich ein neues Tabellenblatt im gleichen Monat an sollen die Daten addiert werden.Ist es ein neuer Monat werden sie dort eingetragen.Die Schwierigkeit liegt wohl darin.das Excel erkennen muss da ist ein neues Tabellenblatt mit dem und dem Datum jetzt muss ich die neu eingetragenen Daten ueber das Datum dem richtigen Monat zuordnen.

Ich habe mehere solcher Arbeitsmappen aber die vorgehensweise ist immer die gleiche.Also haette ich 1 fertiges Beispiel koennte ich es auf die anderen uebertragen.


Die beschriebene Methode von dir konnte ich wunderbar in einer anderen Arbeitsmappe anwenden.Aber da kommen keine neuen Tabellenblatter dazu sondern es werden nur die Daten selbst in den vorhandenen Tabellenblaettern geaendert und da passt das.

lg Klaus

[Beverly]

Hi Klaus,

verstehe ich das richtig, dass du die Tabellenblätter nach dem aktuellen Datum benennst?
Schön wäre es, wenn du eine Beispielmappe (Grafiken müssen nicht enthalten sein) mit mindestens 1 zu kopierenden Tabellenblatt und dem Tabellenblatt mit dem Diagramm hochladen würdest, dann muss man das nicht alles nachbauen und kann am Original testen.

Bis später,
Karin

[criterion]

Hi Karin.
ja das ist richtig ich benenne die Blaetter nach dem aktuellen Datum an dem ich die Inspection durchfuehre.

Muss mal schauen wie man das macht  mit dem hochladen und werde dann die dementsprechende mappe mit Blaettern hochladen,wenn ich nach hause komme.

Danke fuer deine Hilfe.

lg Klaus

[criterion]

Hier eine Arbeitsmappe mit abellenblättern.Habe alles belassen bis auf die Bilder.

lg Klaus

[Beverly]

Hi Klaus,

kopiere diesen Code ins Codemodul DieseArbeitsmappe:

Code: [Auswählen]

Option Explicit
Option Base 1

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim arrMonate
   Dim arrDaten(1 To 4)
   Dim lngZeile As Long
   Dim bytMonat As Byte
   If Sh.Name <> "Summary" Then
      If (Target.Column = 4 Or Target.Column = 5 Or Target.Column = 7 Or Target.Column = 9) And Target.Row > 8 And Target.Row < 31 Then
         For Each Sh In Worksheets
            If Sh.Name <> "Summary" Then
               If Month(Mid(Sh.Name, InStrRev(Sh.Name, " "))) = Month(Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " "))) Then
                  arrDaten(1) = arrDaten(1) + Sh.Range("D31")
                  arrDaten(2) = arrDaten(2) + Sh.Range("E31")
                  arrDaten(3) = arrDaten(3) + Sh.Range("G31")
                  arrDaten(4) = arrDaten(4) + Sh.Range("I31")
               End If
            End If
         Next Sh
         bytMonat = Month(Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " ")))
         With Worksheets("Summary")
            arrMonate = Application.Transpose(.Range("E9:E20"))
            lngZeile = .Range("E9:E20").Find(arrMonate(bytMonat), lookat:=xlWhole).Row
            .Cells(lngZeile, 6).Resize(1, 4) = arrDaten
         End With
      End If
   End If
End Sub
Sobald du in einem der Tabellenblätter in Spalte D, E, G oder I einen Eintrag tätigst, wird in Tabelle "Summary" die Zeile des entsprechenden Monats ausgefüllt/geändert.
Ich habe keine Prüfung eingebaut, ob die letzen 10 bzw. letzten 12 Zeichen des Namens des Tabellenblattes auch wirklich ein Datum ergeben. Eventuell musst du das noch einbauen.

Bis später,
Karin

[criterion]

Hallo Karin.
Habe leider überhaupt kein Ahnung von VBA.
Also habe die Arbeitsmappe geöffnet dann mit Alt F11 den VBA Editor geöffnet und deine Anweisung in das Tabellenblatt Summary kopiert.Dann den Editor geschlossen.
Es ist nichts im Chart passiert.Auch nicht nachdem ich einen Wert verändert habe.Wenn ich ein Tabellenblatt anlege mit Werten verändert sich auch nichts.

Muss ich nochwas ändern und wie?

lg Klaus

[Beverly]

Hi Klaus,

du hast meinen letzten Beitrag auch richtig gelesen? Das steht nicht Tabellenblatt Summary sondern DieseArbeitsmappe 

Der Code soll doch ausgelöst werden, sobald du in einem Tabellenblatt mit deinen Monatsdaten etwas änderst und nicht im Tabellenblatt Summary - also müsste Code ins Codemodul der Monatstabellenblätter, und zwar konkret ins Worksheet_Change Ereignis ("Veränderung im betreffenden Tabellenblatt"). Da dies aber auf jedem Monatstabellenblatt passieren soll, müsste der Code auch in jedem Monatstabellenblatt stehn. Durch Kopieren eines Tabellenblattes würde er dann mit in das neue Blatt übernommen werden und dann dort genau so funktionieren. Du müsstest also Code für das Worksheet_Change Ereignis erst einmal in jedes deiner bereits vorhandenen Tabellenblätter kopieren, ehe dort etwas passieren kann.

Es gibt jedoch eine andere Möglichkeit, wo man sich das sparen kann, da der Code ja eben in (fast) allen Tabellenblättern benötigt wird - in dem Fall stellt Excel das Workbook_SheetChange Ereignis ("Veränderung in allen Tabellenblättern der Arbeitsmappe") zur Verfügung, welches nicht im Codemodul eines Tabellenblattes sondern im Codemodul DieseArbeitsmappe anzusiedeln ist, da er für alle Tabellenblätter gilt. Ausnahmen kann man im Code festlegen - If Sh.Name <> "Summary" Then nur wenn der Name des auslösenden Tabellenblattes nicht Summary ist.

Und genau die als zweites beschriebene Möglichkeit habe ich benutzt, um dir die Arbeit zu ersparen, den Code in jedes Tabellenblatt kopieren zu müssen und um die Arbeitsmappe nicht mit unnötig vielem Code zu belasten.

Ich hoffe, ich konnte einigermaßen verständlich erklären, warum es auf deine Weise nicht funktionieren kann und was du ändern musst, damit es funktioniert.

Bis später,
Karin

[criterion]

Hallo Karin.
So jetzt scheint es zu funktionieren.Musste nur noch die order in der summary veraendern damit die daten von 1-4 mit dem Topic in der summary uebereinstimmten.
Und muss erstmal einen wert in jedes tabellenblatt eingeben damit das ganze aktiviert wird.

Dann kann ich nur tausend dank sagen fuer deine Muehe.Du hast mir echt viel geholfen und viel Eingabe Arbeit erspart.

1ne frage noch.
Kann ich die Werte einfach erweitern.statt 1to4 1to5 oder 1to6?Falls ich mal mehr daten haben moechte.Muss natuerlich dann bissel was angleichen in deinem Macro.

lg Klaus

[criterion]

Hallo Karin.
Wohl zu frueh gefreut.
Zuhause mit Excel 2010 hat es funktioniert aber in der Firma mit 2007 kommt der Fehler bei Werteingabe.
Runtime error 13 type mismatch und im debugger wird folgende Zeile gelb markiert.

If Month(Mid(Sh.Name, InStrRev(Sh.Name, " "))) = Month(Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " "))) Then

Kein schimmer wieso das hier nicht funktioniert.

lg Klaus

[Beverly]

Hi Klaus,

kann ich leider nicht nachvollziehen, habe es jetzt auch in Excel2007 ohne Fehler getestet. Kann es sein, dass in der Datumsangabe des Tabellenblattnamens ein Fehler ist - z.B. Komma statt Punkt oder ein Leerzeichen zwischen den Zahlen? "Type mismatch" deutet auf so etwas hin.

Bis später,
Karin

[criterion]

Hallo Karin.

Habe es jetzt in verschiedenen Arbeitsmappen ausprobiert die aber das gleiche Thema haben.
Ueberall der gleiche Fehler.

Und in Office 2010 funktioniert es einwandfrei.

Die Tabellenblaetter heissen wie oben gezeigt. z.B."Inspection 23.10.2012"

Werde dir heute abend 2 screenshots hochladen.

lg Klaus


PS:Habe die funktionierende mit 2010 geaenderte Arbeitsmappe auf meinen Firmenrechner kopiert und mit 2007 getestet aber kein Erfolg immer der gleiche Fehler.
Koennte heulen,denn es klappt so prima mit der neueren Excel version.
Kann diese auf dem Firmenrechner nicht installieren.
Kannst du mal deine funktionierende 2007er Arbeitsmappe hochladen und ich teste sie.Vielleicht liegt der Fehler woanders.

[Beverly]

Hi Klaus,

deine Frage verstehe ich nicht - deine Mappe funktioniert bei mir doch in Excel2007, wie soll ich da eine 2007er Mappe hochladen? Oder meinst du eine ins XLSM-Format konvertierte Mappe? Wenn du solch eine bereits vorliegen hast und sie in Excel2007 nicht funktioniert, dann solltest du diese Mappe mal hochladen.

Bis später,
Karin

[criterion]

Hallo Karin.
Da meine mappe bei dir funktioniert meinte ich koenntest du sie hochladen.
Dann teste ich ob sie ebenso bei mir funtioniert.
Habe den Code in der Excel 2013er Version eingefuegt und da funktioniert es wie es soll.
Fuege ich den Code in Excel 2007 ein funktioniert es nicht.

Danke nochmal fuer deine Muehe.

lg Klaus

[Beverly]

Hi Klaus,

wenn der Code in meiner Mappe sowohl in Excel2010 als auch in Excel2007 korrekt arbeitet und deine Mappe bei dir in Excel2010 funktioniert, aber in 2007 nicht, liegt es doch an deinem Excel2007 und nicht an der Mappe. Da wird dir nicht viel helfen, wenn du es mit der versuchst, die ich abgespeichert habe. Aber dennoch - hier ist sie.

Bis später,
Karin

[maninweb]

Hallo criterion...

welche Sprache verwendet ihr im Büro, sowohl für Excel als auch für das Betriebssystem?

Es ist durchaus möglich, dass dann das deutsche Datum anders interpretiert wird, was dann
zum Type Mismatch Fehler führen kann.

Gruß

[criterion]

Hallo.
Also zuhause verwende ich das Betriebsystem in deutsch und in der Firma in englisch.Excel ist beides in english.
Werde aber zuhause auf deutsch umstellen in Zukunft.
Ich denke mal mit der Tastatureinstellung hat das nichts zu tun ob ich deutsch o.english einstelle.
Habe das gerade getestet wenn ich nur die Tastatureinstellung veraendere tut sich nichts.Der Fehler bleibt.
Die Betriebssystem Sprache umzustellen waere zu aufwendig.
Wie kann ich das testen? Oder falls da der Fehler liegt in Ordnung bringen?

lg Klaus

[maninweb]

Hallo

von Tastatureinstellungen habe ich auch nicht gesprochen. Es geht um die Sprache des
Betriebssystems und von Excel, da nun mal ein Datum in US das Muster MM/TT/YYYY hat.

Könntest es mit dem Code testen (den vorherigen Code durch die Version hier unten ersetzen)

Code: Visual Basic

1. Option Explicit
2. Option Base 1
3.  
4. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
5.    Dim arrMonate
6.    Dim arrDaten(1 To 4)
7.    Dim lngZeile As Long
8.    Dim bytMonat As Byte
9.    If Sh.Name <> "Summary" Then
10.       If (Target.Column = 4 Or Target.Column = 5 Or Target.Column = 7 Or Target.Column = 9) And Target.Row > 8 And Target.Row < 31 Then
11.          For Each Sh In Worksheets
12.             If Sh.Name <> "Summary" Then
13.                
14. '              If Month(Mid(Sh.Name, InStrRev(Sh.Name, " "))) = _
15. '                 Month(Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " "))) Then
16.              
17.                If CLng(Mid(Mid(Sh.Name, InStrRev(Sh.Name, " ")), 5, 2)) = _
18.                   CLng(Mid(Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " ")), 5, 2)) Then
19.                  
20.                   arrDaten(1) = arrDaten(1) + Sh.Range("D31")
21.                   arrDaten(2) = arrDaten(2) + Sh.Range("E31")
22.                   arrDaten(3) = arrDaten(3) + Sh.Range("G31")
23.                   arrDaten(4) = arrDaten(4) + Sh.Range("I31")
24.                  
25.                End If
26.             End If
27.          Next Sh
28.          
29. '        bytMonat = Month(Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " ")))
30.         bytMonat = CByte(Mid(Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " ")), 5, 2))
31.          
32.          With Worksheets("Summary")
33.             arrMonate = Application.Transpose(.Range("E9:E20"))
34.             lngZeile = .Range("E9:E20").Find(arrMonate(bytMonat), lookat:=xlWhole).Row
35.             .Cells(lngZeile, 6).Resize(1, 4) = arrDaten
36.          End With
37.       End If
38.    End If
39. End Sub
40.  

Gruß

[criterion]

Hi Ihr.
So funktioniert es jetzt in der Firma unter dem englischen Excel.

Tausend Dank nochmal fuer die Muehe.
Halt schade das es nur in deutsch oder englisch geht und nicht mit beiden Sprachen gleichzeitig.
Dann koennte ich die Dateien einfach kopieren und auf beiden Rechnern anwenden.

Oder kann man einfach zuhause die Sprache von deutsch auf english wechseln und muss man das in Windows und Excel oder reicht es nur in Excel umzustellen?

lg Klaus

[Beverly]

Hi Klaus,

mich würde interessieren, ob der folgende Code in beiden Sprachversionen funktionert:

Code: [Auswählen]

Option Explicit
Option Base 1

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Dim arrMonate
   Dim arrDaten(1 To 4)
   Dim lngZeile As Long
   Dim bytMonat As Byte
   If Sh.Name <> "Summary" Then
      If (Target.Column = 4 Or Target.Column = 5 Or Target.Column = 7 Or Target.Column = 9) And Target.Row > 8 And Target.Row < 31 Then
         For Each Sh In Worksheets
            If Sh.Name <> "Summary" Then
               If Mid(Sh.Name, InStrRev(Sh.Name, " ")) = Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " ")) Then
                  arrDaten(1) = arrDaten(1) + Sh.Range("D31")
                  arrDaten(2) = arrDaten(2) + Sh.Range("E31")
                  arrDaten(3) = arrDaten(3) + Sh.Range("G31")
                  arrDaten(4) = arrDaten(4) + Sh.Range("I31")
               End If
            End If
         Next Sh
         bytMonat = Month(DateValue(Mid(ActiveSheet.Name, InStrRev(ActiveSheet.Name, " "))))
         With Worksheets("Summary")
            arrMonate = Application.Transpose(.Range("E9:E20"))
            lngZeile = .Range("E9:E20").Find(arrMonate(bytMonat), lookat:=xlWhole).Row
            .Cells(lngZeile, 6).Resize(1, 4) = arrDaten
         End With
      End If
   End If
End Sub

Bis später,
Karin

[maninweb]

Hallo Klaus...

unabhängig von der Nachfrage von Beverly (Beitrag zuvor), müsste meine Variante unter
beiden Versionen (DE & EN) laufen (ohne ein Umstellen von Excel) denn diese extrahiert
den Monat als String. Vorausgesetzt, das Muster für die Tabellenbezeichnungen ist
immer gleich: "Bezeichnung TT.MM.JJJJ"

Gruß

[Beverly]

Vorausgesetzt, das Muster für die Tabellenbezeichnungen ist
immer gleich: "Bezeichnung TT.MM.JJJJ"

Das war in der Ursprungsmappe genau nicht der Fall, weshalb ich meinen Code anders geschrieben und mich nicht auf die Anzahl an Stellen bezogen hatte.

Bis später,
Karin

[maninweb]

Hi Beverly...

Ah, Ok. Das war mir jetzt nicht so bewusst bzw. hatte ich nach geschaut.

Gruß

[criterion]

Am WE werde ich das mal testen

lg Klaus

[criterion]

Funktioniert in beiden Versionen.
In English und deutsch.

Tausend dank nochmal euch beiden.


lg Klaus