PDA

Vollständige Version anzeigen : mehrtägige Veranstaltung automaisch in Kalender eintragen


now_me
08.07.2014, 18:29
Hallo Zusammen,

ich habe eine Blatt Veranstaltungsliste mit
A=Von Datum
B=Bis Datum
C=Veranstaltungs Name

1.1. 3.1 x

ein Blatt Kalender mit einer Tagesansicht (Spalten) pro Monat (Zeilen)
In etwa so
Jan 1 2 3
___ x
Feb 1 2 3

Nun will ich in diesen Kalender die Veranstaltungen eintragen lassen.
Mit SVerweis bekomme ich aber immer nur den 1. Tag (Von) hin.
Was muss ich machen, dass ich Veranstaltung x vom 1.1 bis 3.1 im Kalender angezeigt bekomme???

Freue mich auf Eure Hilfe

Mc Santa
08.07.2014, 18:39
Hallo,

Kannst du eine Tabelle mit dem Layout und ein paar Beispieldaten hochladen?

Und was passiert, wenn sich zwei Veranstaltungen überschneiden? Ich versuche eine Lösung per Formel, aber ist auch eine Lösung per VBA ok?

VG

now_me
08.07.2014, 19:17
Hallo Mc Santa,

Danke für die schnelle Antwort :D . Die Datei ist anbei.
Wenn (unwahrscheinlich) sich Veranstaltungen überschneiden,
dann würde ich dies in mehreren Zeilen untereinander im Kalender anzeigen wollen.
Zur Zeit ist die erste Zeile für die Feiertage, die 2-5 für Veranstaltungen.

LG, Now_me

Mc Santa
08.07.2014, 21:05
Hallo,

folgende Möglichkeit mit Formeln, aber mit zwei Einschränkungen:
Die Veranstaltungen müssen nach Beginn aufsteigend sortiert sein.
Und Überschneidungen werden nicht richtig angezeigt.
<br/><b><em>Kalender</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:76px;" /><col style="width:76px;" /><col style="width:76px;" /><col style="width:76px;" /><col style="width:76px;" /><col style="width:76px;" /><col style="width:76px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>I</td><td>J</td><td>K</td><td>L</td><td>M</td><td>N</td><td>O</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">31</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">08</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">09</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">14</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">32</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Fr</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Sa</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">So</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Mo</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Di</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Mi</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Do</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">33</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Christi Himmelfahrt</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">34</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">BB</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">BB</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">35</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">36</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">37</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">38</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">08</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">09</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">10</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">11</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">12</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">13</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">14</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">39</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Mo</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Di</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Mi</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Do</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Fr</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Sa</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">So</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">40</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">41</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">AA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">AA</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">42</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">43</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">44</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td><td style=" color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>I31:O31,I38:O38</td><td>=+H31+1</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>I32:O32,I39:O39</td><td>=+I31</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>I33:O33,I40:O40</td><td>=WENNFEHLER(SVERWEIS(I31;Feiertage!$A:$I;9;0);"")</td><td>&nbsp;</td><td>&nbsp;</td></tr><tr><td>I34:O34,I41:O41</td><td>=WENNFEHLER(WENN(INDEX(Veranstaltungen!$B:$B;VERGLEICH(I31;Veranstaltungen!$A:$A ;1);0)&gt;=I31;INDEX(Veranstaltungen!$C:$C;VERGLEICH(I31;Veranstaltungen!$A:$A;1);0 );"");"")</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.01 einschl. 64 Bit</td></tr></table><br/>

Für eine Version ohne diese Einschränkungen müsste man wohl VBA bemühen. Ich glaube aber, dass das ein bisschen aufwendiger ist.

Hilft dir das weiter?
VG

now_me
11.07.2014, 08:44
HAllo Mc Santa,

das sieht doch schon ganz toll aus. Vielen Dank!
Das mit dem "Liste muss sortiert sein" ist kein Problem.
Aber fällt Dir noch was ein, dass ich eine Formel in die Zeile darunter eingeben kann für überschneidende Termine???

Danke schon mal, Lg, now_me

Mc Santa
11.07.2014, 09:12
Hallo,

ich denke nur über VBA, aber mir ist die Programmierung zu aufwendig, sorry.

VG

now_me
10.10.2014, 16:30
Hallo Alle,

ich habe jetzt in meiner VA Datei eine Hilfsspalte, die bei Überschneidungen ein "X" anzeigt.
Im Kalender habe ich jetzt eine Matrixformel, die in einer Zeile die Termine ohne x prüft, in der zweiten Zeile die Termine mit x.
{=WENNFEHLER(INDEX(Veranstaltungen!$D:$D;KKLEINSTE(WENN((Veranstaltungen!$A:$A<=B3)*(Veranstaltungen!$C:$C="X")*(Veranstaltungen!$B:$B>=B3);ZEILE($1:$100));ZEILE($A$1)));"")}

Das funktioniert auch prima - nur dauert das jetzt ewig :rolleyes: lang zu aktualisieren...
Hat jemand eine Idee??

Freue mich auf Euren Experten Rat.

Danke und Gruß,
now_me