Monatsplanung Einsatzortscharf aus Jahresplanung ausgeben

[Seeqi]

Hallo liebe Zellenfreunde,

Ich komme mal wieder an meine Grenzen, und da mir das letzte mal so gut geholfen wurde, dachte ich ich probiere es noch einmal :D
In meinem letzten Post ging es darum, dass ich gerne eine Mitarbeiterscharfe Monatsplanung aus einer bestehenden Jahresplanung ausgegeben haben möchte :) 

Das ganze funktioniert auch mittlerweile echt super und ich konnte das auf eine Ausgabe von einer Mitarbeiterscharfen Jahresplanung erweitern.

So nun stelle ich mir die Frage, ob es möglich ist das ganze auch Einsatzort scharf zu erstellen   ?

Anbei findet ihr die Testdatei in welcher die Mitarbeiterscharfe Planung bereits eingebaut wurde.

Neu sind die Zelle A7 so wie der Bereich "Einsatzortscharfe Monatsplanung" und im Reiter "Jahresplanung" die Spalte "Einsatzort".

Im Idealfall soll er in Zelle A7 erkennen welchen Einsatzort ich benötige und dann erkennt welche Mitarbeiter dort einbezogen werden müssen und mir dann einen "Dienstplan" für den Monat anzeigt.
Sowohl die geplanten Schichten als auch die Zeile für die Urlaubs und Kranken Eintragungen.

Falls es mit den normalen Formeln nicht möglich ist, würde ich mich auch über eine VBA Lösung freuen :)
In dem Finalen Dispotool befinden sich auch schon kleinere VBA Module   

Sollte jedoch nicht zu kompliziert werden oder falls doch wäre es schön wenn sich jemand dann die Zeit nehmen könnte mir den Code zu erklären   

Dann wünsche ich nun viel Spaß beim knobeln und bin auf die Lösungsansätze gespannt :D 

Mit freundlichen Grüßen

Seeqi

[Seeqi]

Keiner einer Idee ? 

[ralf_b]

Ideen schon , aber nicht immer die Lust dazu. 

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)


    If Target.Address = "$A$7" Then
        Planeintragen Target.Value
    ElseIf Target.Address = "$A$3" Then
        Planeintragen Range("A7").Value
    End If
   
End Sub

Sub Planeintragen(sOrt As String)


    Dim rng    As Range
    Dim i As Long, lrow As Long, firstrow As Long
    Dim firstdate As Date, lastdate As Date
    Dim firstcol As Variant
    Dim iDateDiff As Integer

    Application.EnableEvents = False
   
    firstrow = 18
    lrow = Cells(Rows.Count, "E").End(xlUp).Row
   
    If lrow >= firstrow Then
        Rows(firstrow & ":" & lrow).ClearContents
    End If
   
    lrow = firstrow
   
    With Worksheets("Jahresplanung")
        If sOrt <> "" Then
           
            firstdate = Range("F16")
            lastdate = Cells(16, Columns.Count).End(xlToLeft)
   
           
            iDateDiff = DateDiff("d", firstdate, lastdate)
           
            firstcol = Application.Match(CDbl(firstdate), .Rows(2), 0)
            If IsError(firstcol) Then Application.EnableEvents = True: Exit Sub
           
            For Each rng In .Range("B3:B11").Cells
                If rng.Value = sOrt Then
                    rng.Offset(0, 1).Resize(2).Copy Range("E" & lrow)
                    .Cells(rng.Row, firstcol).Resize(2, iDateDiff).Copy Range("F" & lrow)
                    lrow = lrow + 2
                End If
               
            Next
        End If
    End With
    Application.EnableEvents = True
       
End Sub

[Fred11]

Hallo seeqi

noch eine Idee:
mit der INDEX/AGGREGAT-Formel  kannst du in Zelle E18  und folgende alle Namen und Personalnummern aus Tabellenblatt "Jahresplanung"  auflisten, die zu dem in A7 eingetragenen Einsatzort gehören. Die Übertragung der restlichen Daten (Schichten und Urlaub-/ Kranktage) dann wie gehabt mit INDEX/VERGLEICH.

Was allerdings bei deinem aktuellen  Tabellenaufbau erschwerend hinzuzukommt, ist, dass auf dem Tabellenblatt "Jahresplanung" der Einsatzort nur vor dem Namen steht, und nicht vor der Personalnummer.

Damit das mit dem Auflisten der Namen und der entsprechenden Personalnummer funktioniert, sollte deshalb auf dem Tabellenblatt "Jahresplanung" der Einsatzort nicht nur vor dem Namen, sondern auch vor der Personalnummer stehen.
Ansonsten muss man da bei der INDEX/AGGREGAT-Formel noch etwas tricksen.

Erklärung der INDEX/AGGREGAT-Formel siehe hier:
https://thehosblog.com/2013/05/08/excel-...-aggregat/
https://www.youtube.com/watch?v=He3dblboncw

Arbeitsblatt mit dem Namen 'Übersicht Beispiel'
	E	F	G	H	I	J	K	L	M	N	O
16		1.1	2.1	3.1	4.1	5.1	6.1	7.1	8.1	9.1	10.1
17		Fr	Sa	So	Mo	Di	Mi	Do	Fr	Sa	So
18	Max Mustermann	1	1	1	R	R	1	1	1	R	R
19	153687	u	u	u	u	u	u	u	u	u	u
20	Peter Petersilie	2	2	4	5	6	R	R	1	1	1
21	154863
22	Tom Tomatenmark	1	1	1	R	R	1	1	1	R	R
23	145863						K	K	K	K	K

Name	Bezug
Matrix_Jahresplanung	=Jahresplanung!$D$3:$BL$12

Zelle	Formel
E18	=WENNFEHLER(INDEX(Jahresplanung!$C$3:$C$12;AGGREGAT(15;6;ZEILE(Jahresplanung!$C$3:$C$12)/((Jahresplanung!$B$3:$B$12=$A$7)+(Jahresplanung!$B$2:$B$11=$A$7))-2;ZEILE(A1)));"")
F18	=WENN($E18<>"";INDEX(Matrix_Jahresplanung;VERGLEICH($E18;Jahresplanung!$C$3:$C$12;0);VERGLEICH('Übersicht Beispiel'!F$16;Jahresplanung!$D$2:$BL$2;0))&"";"")

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruss
Fred

[Seeqi]

Wow vielen Dank ihr beiden :D