# Zellen im dynamischen Kalender kopieren



## donsenilo82 (27. August 2013)

Hallo Community, bin neu hier und hab mich schon ein wenig durch die Beiträge gelesen, doch das was ich suche leider nicht gefunden.

Ich habe im Excel einen dynamischen Kalender erstellt, wie folgt:

Zelle A1: Jahr Zelle B1: 2014
                      Zelle B2: =DATUM(B1;1;1)


Wenn ich jetzt die Zelle B2 am rechten Kästchen unten nach unten ziehe kopiert excel breav die Formel und passt sie für die jeweilige Zelle weiter an.

JETZT MEIN PROBLEM:

Zwischen den einzelnen Datumsangaben befinden sich jeweils 47  Zellen in denen sich ein Tageskalender in Tabellenform befindet: 

wie kann ich die Daten komplett nach unten erweitern, das heißt Zelle 2 bis Zelle 49 kopieren und von Zelle 51 bis Zelle 98 Einfügen UND die eine Formel kopieren und erweitert formatiert einfügen****** Oder muss ich jetzt die Daten durch ziehen kopieren und die einzelnen Tagesformeln (365!?) von Hand eintragen?  Bitte helft mir.....

Ich mein ich machs auch von Hand aber da sitz ich ne Weile, und da gibts doch bestimmt nen Kniff....

Des Weiteren möchte ich die Monate in einzelnen Tabellenblättern trennen, d.h. Blatt 1 = Januar, Blatt 2 )= Februar, etc... wie kann ich die Formel ändern, dass bei Februar die erste Datumszelle nicht wieder 1. januar sondern 1. Februar ist?

PS.: Bliebe evt. noch zu erwähnen, dass ich mich mit Excel in der Regel nur beschäftige, wenn ich sowas erstellen will, nicht hobbymäßig oder professionell, und leider schon gar nicht regelmäßig...


----------



## tombe (27. August 2013)

Bereich markieren und kopieren. Dann die Zelle markieren wo der nächste Kalender eingefügt werden soll (z.B. A50) und dann einfach einfügen!?


----------



## donsenilo82 (27. August 2013)

macht er nicht, er bringt dann in der entsprechenden Zelle 1. Januar 1900
ich will aber 2. Januar 2014


----------



## tombe (27. August 2013)

Hm, kannst du die Datei mal im Excel 2000 Format speichern und hier einstellen? Ich kann mir nicht so richtig vorstellen wie es genau aussieht.


----------



## donsenilo82 (27. August 2013)

Excel 97-2003 Arbeitsmappe?

Habs als Rar-Datei hochgeladen

Excel geht net, oder ich bin zu doof


----------



## sheel (27. August 2013)

Hi

Stimmt, XLS geht zurzeit nicht.
Rar sollte aber gehen, und geht laut dir ja auch,
nur seh ich keine Datei. Vergessen?


----------



## donsenilo82 (28. August 2013)

ne, ganz oben im Post


----------



## tombe (28. August 2013)

Versuche mal folgendes.

1) Zelle B1 bekommt die Jahreszahl, zum Bespiel 2013

2) In Zelle B2 trägst du die Formel *DATUM($B$1;1;1)* wie gehabt ein.

3) In Zelle B51 trägst du folgende Formel ein *DATUM($B$1;1;((ZEILE(B51)-2)/49)+1)*

4) In die Zeilen darunter (Zeilen 52 bis 89) kopierst du dann die Namen und Zeiten.

5) Du markierst den kompletten Bereich von Zeile 51 bis 89 und kopierst ihn.

6) Alle 49 (!!) Zeilen fügst du diesen markierten Bereich ein.

Die Formel *DATUM($B$1;1;((ZEILE(B51)-2)/49)+1)* macht folgendes.

- Parameter 1 holt immer die Jahreszahl aus Zelle B1.

- Parameter 2 ist hier immer die Zahl 1 für Januar. Wobei es eiinfacher wäre wenn du z.B. in Zelle C1 die Monatszahl eintragen würdest und anstatt dem festen Wert 1 in der Formal dann den Zellbezug C1 nimmst!

- Parameter 3 rechnet die Zeilenzahl in den entsprechenden Tag um.
Im obigen Beispiel ist es die Zeile 51. Davon wird zuerst 2 abgezogen weil es oben in der Tabelle 2 "Anfangszeilen" gibt. Dieser Wert wird durch die Zahl 49 geteilt weil es sich alle 49 Zeilen wiederholt (muss eventuell angepasst werden wenn sich der Bereich ändert). Zu diesem Ergebnis muss dann noch der Wert 1 dazugezählt werden damit es stimmt.


----------



## donsenilo82 (28. August 2013)

Danke, du bist mein Held.... wirklich, ich glaub an der Formel hätt ich mir mit meinem aktuellen Wissensstand über Excel die Zähne ausgebissen....


Und wie bekomme ich es hin,dass in den anderen Tabellenblättern der jeweilige Monat übernommen wird und nicht jedes mal mit Januar beginnt? 

warte, ich glaub ich habs verstanden.... 

DATUM($B$1;2;((ZEILE(B51)-2)/49)+1)

für Februar

DATUM($B$1;3;((ZEILE(B51)-2)/49)+1)

für März

usw.... ****?


----------



## tombe (28. August 2013)

Genau so musst du es für die anderen Monate ändern.

Aber um die Formel unverändert auf alle Monate übernehmen zu können würde ich wie oben geschrieben die Monatszahl auch in die Tabelle schreiben und nicht in die Formel

DATUM($B$1; *$C$1*; ((ZEILE(B51)-2/49)+1)

Hier wird die Jahreszahl aus Zelle B1 und der Monat aus Zelle C1 geholt. Dann kannst du es ohne zu ändern für jeden Monat nehmen. Einfach den entsprechenden Wert in die Zellen eintragen und fertig.


----------



## donsenilo82 (31. August 2013)

Super, klappt bestens, würde gern 100x den Danke Button drücken, geht aber nur einmal, also nochmal n dickes DANKEEEEEE

Also wie gehabt, hat alles wunderbar funktioniert .... Danke nochmal. Jetzt hätte ich aber evt. noch gern automatisch Geburtstage und Feiertage aus einem 13. & 14 Arbeitsblatt oder ähnlich am jeweils passenden Tag eingetragen und Feiertage farbig von anderen abgehoben... kannst mir da  bitte NOCHMAL WEITERHELFEN ****?


----------



## tombe (3. September 2013)

Also für das Formatieren der Feiertage gibt es eine recht einfache Lösung. Wie man sie für deinen Fall umsetzen kann, hängt davon ab wie die Feiertage in der Tabelle enthalten sind.


```
=SVERWEIS($B2;Feiertage;1;0)
```

Die obige Formel gibst du bei "Bedingte Formatierung" ein. Wobei hier nicht "Zellwert ist" sondern "Formel ist" ausgewählt werden muss.

In diesem Beispiel steht in Zelle B2 das Datum. Dann wird in der Tabelle wo die Feiertage stehen der Name "Feiertage" für diesen Bereich vergeben. Durch die Funktion SVERWEIS wird jetzt ermittelt ob das Datum in dieser Tabelle vorhanden ist. Für diesen Fall gibst du jetzt die Formatierung (Farbe, Schriftart usw.) an.

Das ist ein bisschen blöd mit Worten zu erklären. Schau mal ob du damit klar kommst, wenn nicht schauen wir wo es hängt.

Das mit den Geburtstagen macht mir größere Probleme. Es lässt sich im Grunde zwar genauso einfach mit SVERWEIS lösen. Aber da nicht bekannt ist wie viele Geburtstage existieren, wird es schwierig die dann korrekt anzuzeigen. Gibt es da ein Limit, das beispielsweise immer für 2, 5, 10 Personen der Geburtstag gespeichert wird?


----------



## donsenilo82 (3. September 2013)

Also da meine Frau ne recht Große Sippe mit eingebracht hat, ist die Familie sprunghaft von 17 nahen Verwandten auf +über 60 gewachsen, dementsprechend kommen auch relativ schnell wieder neue hinzu, weil die nächste Generation jetzt am "Vermehren" ist - ich würde mal ne Zahl von 100 pauschalisieren... ich könnte ev. noch ne Datei hochladen, in der ich evt. die meisten Geburtstage schon eintrage, in nem extra Blatt am Ende des Kalenders....****?

Das andere probier ich nachher gleich mal, vielleicht krieg ichs ja gebacken. ;o)
Aber schon mal danke, dass du dir überhaupt die Mühe machst, über meine irrwitzigen Hirngespinste das Hirn zu zermartern....


----------



## tombe (3. September 2013)

Komisch, komisch.
Die Formel die ich oben geschrieben habe, hat bei mir zuerst funktioniert. Jetzt aber tut sie es nicht mehr!?


```
=NICHT(ISTFEHLER(SVERWEIS(TEXT(TAG($B2);"TT") & "." & TEXT(MONAT($B2);"MM") & "."; Feiertage;1;0)))
```

Diese Formel funtioniert aber wirklich. Die Feiertagsdaten müssen im Format "*TT.MM.*" inklusive der Punkte eingegeben werden und die Zellen müssen als Text formatiert werden damit Excel nicht selber ein Datum daraus macht.

Diese bedingte Formatierung kannst du dann ja auf alle Zellen übertragen in denen ein Datum steht.


----------



## donsenilo82 (4. September 2013)

Also, hab jetzt in der Tabelle  ein 13. Arbeitsblatt "Termine" vergeben, in dem die Feiertage wie folgt zu sehen sind (mit Formeln errechnet) (Siehe Anhang)

Jetzt hab ich wieder n paar Problemchen: 
Bereich "Feiertage"  definiert und benannt (A3-B35)

Deine Formel =SVERWEIS($B2;Feiertage;1;0)... wo geb ich die ein, bei bedingte Formatierung kann ich nix eingeben, oder hab ich was falsch verstanden?

Wenns dann klappt, muss ich die bedingte Formatierung auf jeder Datumszelle anwenden? (B2, B51, B100, usw)?

Gruß


----------



## donsenilo82 (4. September 2013)

;-) 
PS.: Rechtschreibfehler in F6 ist bereits behoben


----------



## tombe (4. September 2013)

In den Tabellen Januar, Februar, März, ... steht ja immer das jeweilige Tagesdatum.

Hier klickst du eine Zelle an (am besten die erste) und gehst dann auf "Bedingte Formatierung". Eigentlich sollte das gehen, wenn nicht: gute Frage!?

Wenn es geht, musst du wie richtig erkannt, das dann auf alle anderen "Datumsfelder" übertragen.

Ich würde dir raten erstmal nur die Tabelle Januar fertig zu machen. Wenn da alle "Funktionen" enthalten sind und alles richtig läuft, kannst du diese Tabelle kopieren und für die restlichen Monate anpassen. Dürfte einfacher sein als alle Änderungen in 11 Monaten durchzuführen.


----------



## donsenilo82 (4. September 2013)

mit die erste Zelle meinste A1 oder die erste benutzte Datumszelle, in dem Fall B2?


worauf soll ich klicken (Siehe Grafik)?


----------



## tombe (4. September 2013)

*Neue Regel...* da du ja jetzt eine neue bedingte Formatierung für diese Zelle erstellen willst!

Ach so ich meinte B2, die Zelle in der das erste Datum steht!


----------



## donsenilo82 (4. September 2013)

und hier, kann immernoch nix eingeben, was muss ich denn jeweils oben und unten auswählen?


----------



## tombe (4. September 2013)

Nimm den letzten Eintrag "Formel zur Ermittlung ..." denn du willst ja eine Formel eingeben!

Ich habe übrigens auch eine Lösung für das "Geburtstagsproblem". Allerdings habe ich hierzu keine Formel oder ähnliches genommen, sondern mit einer Benutzerfunktion.

Blöd nur das ich das an einem Rechner gemacht habe an den ich erst morgen wieder dran komme. Vielleicht kannst du bis dahin die aktuelle Version deiner Datei nochmal hier einstellen. Dann würde ich darin soweit mal alles "einbauen" und es hier beschreiben wie es gemacht ist.

So ich schau jetzt "Under the Dom". Gute Nacht!


----------



## donsenilo82 (4. September 2013)

Under The Dome? Hab ich gestern gelesen, klingt interessant, werd ich mir auch mal einverlaiben... Datei ist UP!


----------



## donsenilo82 (5. September 2013)

Hoppla, Upload fehlgeschlagen? Warum?#

kann ichs dir woanders hin schicken?


----------



## tombe (5. September 2013)

*1) Geburtstage: Hierfür habe ich eine Funktion erstellt die als Parameter das jeweilige Datum enthält.*


```
Function geburtstagsliste(datum As Date)

'Zeilenzähler, beginnt in Zeile 2, da Zeile 1 als Überschrift dient.
Dim zeile As Integer
zeile = 2
'Hier werden die Namen und die weiteren Angaben gespeichert um sie dann anzuzeigen.
Dim ausgabe As String
ausgabe = ""

'Geburtstagstabelle so lange durchlaufen bis in Spalte A kein Eintrag mehr vorhanden ist.
While Tabelle14.Cells(zeile, 1).Value <> ""
    If datum = Tabelle14.Cells(zeile, 3).Value Then
    'Wenn Datum übereinstimmt, Namen und Alter speichern.
        ausgabe = ausgabe & Tabelle14.Cells(zeile, 2).Value & " (" & Tabelle14.Cells(zeile, 4).Value & "), "
    End If
    zeile = zeile + 1
Wend

'Gefundene Werte zurückgeben und anzeigen.
'Der Text wird um 2 Zeichen gekürzt, da am Ende noch ein Komma und Leerzeichen ist.
If Len(ausgabe) > 2 Then ausgabe = Left(ausgabe, Len(ausgabe) - 2)

geburtstagsliste = ausgabe

End Function
```

Die Daten stehen in folgender Form in der "Geburtstagstabelle":
- Spalte A, enthält das tatsächliche Geburtsdatum
- Spalte B, enthält den Namen
- Spalte C, rechnet das Geburtsdatum auf das aktuelle Jahr um, damit es mit dem Kalender verglichen werden kann
- Spalte D, berechnet das Alter

*2) Feiertage:* In der Tabelle "Feiertage sind alle entsprechenden Daten eingegeben, Spalte A enthält das Datum, Spalte B die Bezeichnung. Wichtig ist das die Spalte A den Namen *Feiertag* bekommen hat. Über diesen Namen wird sie in der Formel angesprochen.


```
=NICHT(ISTFEHLER(SVERWEIS($B2; Feiertag; 1; 0)))
```

Mit dieser Formel wird die bedingte Formatierung aller Felder belegt (hier die Zelle B2) in denen ein Datum angezeigt wird und wo Feiertage hervorgehoben werden sollen.

In der angehängten Beispieldatei sind die Monate Januar und Februar enthalten. Außerdem sind einige Feiertage und Geburtstage zum Testen in den jeweiligen Tabellen eingetragen.

Schau ob du alles verstehst und auf deine Datei umsetzten kannst.

Gruß Thomas


----------



## donsenilo82 (8. September 2013)

Moin, hab ne Weile gefummelt, aber da ich in der ein oder anderen Zelle schon ne andere Formatierung habe, klappt net alles...

1.)  
Geburtstage und die entsprechenden Spalten hab ich so formatiert, wie von dir vorgegeben.... - Nur wo geb ich jetzt deine Funktion ein, und was davon (alles oder alles außer dem grün geschriebenen, oder wie?) :cofused:

2.) Bei den Feiertagen, hab ich die Spalten verdreht zu deiner Formatierung, also Erst die Bezeichnung des Feiertages, in Spalte A, in Spalte B das Datum (verschiedenen Daten anhand von Formeln berechnet, und so formatiert, dass wenn ich bei Januar B1 die Jahreszahl ändere, die Jahreszahl auch bei Februar bis Dezember + Feiertage automatisch übertragen wird und so die Tage für die Monate und auch die Daten für die Feiertage automatisch neu errechnet werden.

Im Übrigen, hatte ich mir gedacht, dass die bedingte Formatierung nicht nur die Zelle farbig markiert, sondern zusätzlich in die jeweilige Zelle, oder die darauf folgende Zelle den genauen Text aus der jeweiligen zutreffenden Zelle aus Spalte A bei "Feiertage" ZUSÄTZLICH zum Datum einträgt (also z.Bsp.: Montag 01.Januar 2021 Neujahr - oder Donnerstag 29. Mai 2014 Christi Himmelfahrt, eben in der jeweiligen Formatierung im Blatt "Feiertage")

Als weiteres Feature, das ist mir aber erst gestern eingefallen, hätte ich gern wenn möglich vor dem jeweiligen tag die Kalenderwochen angegeben (KW1, KW 3, KW48 usw.)



Im Anhang eine aktuelle Version meiner Datei, vielleicht kannste mit de bereits vorhandenen Geschichte die Sache bissle einfacher umsetzen.. (einfacher für dich, für mich sinds ja nach wie vor alles bömische Dörfer)


----------



## tombe (9. September 2013)

Im Anhang findest du deine Datei, diesmal im aktuellen Excel-Format. Hier sind jetzt alle "Funktionen" in den Monaten Januar bis März enthalten.

zu 1) Klick einfach mal eine der "Geburtstagszellen" an. Dann siehst du was du eingeben müsstest bzw. wie die Funkion aufgerufen wird. Den obigen Code musst du in ein Modul kopieren. Hierzu auf Entwicklertools -> Visual Basic klicken. Lies dir aber nochmal durch was ich oben zu der Funktion geschrieben habe. Es darf bei den Geburtsdaten KEINE Leerzeile geben, da die Funktion dies als Abbruchbedingung nimmt! Im Moment würden bei dir deshalb auch nicht alle Geburtstage angezeigt!

zu 2) Ich habe die Feiertage jetzt wieder so eingetragen wie ich es in meinem Beispiel hatte. Ist eigentlich gleichgültig, war aber für mich einfacher.

Der jeweilige Name des Feiertages wird jetzt auch angezeigt, genauso die Kalenderwoche.

Gruß Thomas


----------



## donsenilo82 (16. September 2013)

So, ich wollte mich noch mal recht herzlich bedanken, der Kalender ist endlich fertig und funktioniert so, wie ich es mir vorgestellt habe (bis auf die bedingte Formatierung mit der Farbänderung bei den Feiertagen) - aber das krieg ich noch hin. DANKE vielmals....


----------

