Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1456to1460
1456to1460
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

urlaubsplaner

Forumthread: urlaubsplaner

urlaubsplaner
22.11.2015 14:11:19
Simone
Huhu zusammen,
mal wieder habe ich ein Problem.
Spalte A sind Mitarbeiter.
Spalte B ist Abteilung
Spalte C bis Spalte AJ sind die Monatstage. ( 1 bis 31 ).
Nun möchte ich gerne das z.b. Gruppe1 nicht mehr wie fünf Mitarbeiter ein " U" drin steht. Sonst kommt die Meldung :"zu viele Urlaub"
Gruppe2 : nur bis 4
Abteilung : Büro : 2
Abteilung : WE : 3
Kann man das mit der Gültigkeit machen?
Wenn ja, wie mache ich das?
LG Simone

Anzeige

20
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Gültigkeit: =Zählenwenn(Bereich;"U")<5
22.11.2015 15:25:33
Matthias
Hallo
benutzerdefinierte Gültigkeit
Formel:
=Zählenwenn(Bereich;"U")<5
Hier lässt Du also nur 4 x Urlaub zu (einfach dann anpassen)
Gruß Matthias

AW: Gültigkeit: =Zählenwenn(Bereich;"U")<5
22.11.2015 15:47:29
Simone
Danke Matthias,
aber Problem ist , wenn z.b. Gruppe1 5 Urlaub haben, kann ich jetzt vom Büro keinen in Urlaub schicken.
Die Formel z.b. wäre: = Wenn, Gruppe1 bis 5
Urlaub
=wenn Gruppe2 bis 4 Urlaub
=wenn Büro bis 2
=wenn WE bis 3.
Wie mache ich das?
Danke

Anzeige
ZählenwennS
22.11.2015 16:04:30
Matthias
Hallo Simone
Schau Dir mal ZählenWennS an.
Dort kannst Du ja weitere Kriterien angeben, wie z.B die Gruppe
Gruß Matthias

AW: ZählenwennS
22.11.2015 16:07:33
Simone
Ich probiere es mal. melde mich dann nochmal.
Danke

Summenprodukt
22.11.2015 17:50:54
Matthias
Hallo
Ich hab das mal nach Deinen Vorgaben nachgestellt
Allerdings scheint mir ein solcher Aufbau nicht gerade optimal
Mit Summenprodukt mal für "Gruppe2"
Tabelle1

 ABCDE
1MitarbeiterAbteilung123
2MA1Gruppe2u  
3MA2WE   
4MA3Büro   
5MA4Gruppe2uu 
6MA5WE   
7MA6Büro   
8MA7Gruppe2uu 
9MA8WE   
10MA9Büro   
11MA10Gruppe2uu 
12MA11WE   
13MA12Büro   
14MA13Gruppe2u  
15MA14WE   
16MA15Büro   
17MA16Gruppe2 u 
18MA17WE   
19MA18Büro   
20MA19Gruppe2 u 

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C2Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D2Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E2Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C3Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D3Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E3Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C4Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D4Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E4Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C5Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D5Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E5Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C6Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D6Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E6Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C7Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D7Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E7Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C8Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D8Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E8Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C9Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D9Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E9Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C10Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D10Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E10Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C11Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D11Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E11Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C12Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D12Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E12Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C13Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D13Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E13Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C14Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D14Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E14Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C15Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D15Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E15Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C16Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D16Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E16Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C17Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D17Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E17Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C18Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D18Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E18Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C19Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D19Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E19Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 
C20Benutzerdefiniert =SUMMENPRODUKT((C$2:C$20="U")*($B$2:$B$20="Gruppe2"))<=5 
D20Benutzerdefiniert =SUMMENPRODUKT((D$2:D$20="U")*($B$2:$B$20="Gruppe2"))<=5 
E20Benutzerdefiniert =SUMMENPRODUKT((E$2:E$20="U")*($B$2:$B$20="Gruppe2"))<=5 


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Ansonsten unbedingt vorher die Abteilung ausählen
https://www.herber.de/bbs/user/101721.xlsx
Gruß Matthias

Anzeige
ausählen sollte natürlich auswählen heißen ... owT
22.11.2015 20:10:26
Matthias

Beispieldatei? o.T.
22.11.2015 17:09:48
Sepp
Gruß Sepp

Bedingte Formatierung
22.11.2015 18:26:30
Sepp
Hallo Simone,
ich würde das über die bedingte Formatierumg lösen.
Tabelle1

 ABCDEFGHIJKLMNOP
1MitarbeiterAbteilung1234567891011121314
2MA1Gruppe2u             
3MA2WE              
4MA3Büro              
5MA4Gruppe2uu            
6MA5WE    u         
7MA6Büro              
8MA7Gruppe2u             
9MA8WE    u         
10MA9Büro       u      
11MA10Gruppe2uu            
12MA11WE    u         
13MA12Büro       u      
14MA13Gruppe2u             
15MA14WE    u         
16MA15Büro       u      
17MA16Gruppe2 u            
18MA17WE              
19MA18Büro              
20MA19Gruppe2 u            

Datengültigkeit der Tabelle
ZelleZulassenDatenEingabe 1Eingabe 2
C2Benutzerdefiniert =_validate 
Namen in Formeln
ZelleNameBezieht sich auf
C2_validate=SUMMENPRODUKT((Tabelle1!$B$2:$B$20=Tabelle1!$B2)*(Tabelle1!C$2:C$20="u"))>INDEX({4.2.3};VERGLEICH(Tabelle1!$B2;{"Gruppe2"."Büro"."WE"};0))
Namen verstehen

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
C21. / Formel ist =_validate*(C2<>"")Abc
Namen in Formeln
ZelleNameBezieht sich auf
C2_validate=SUMMENPRODUKT((Tabelle1!$B$2:$B$20=Tabelle1!$B2)*(Tabelle1!C$2:C$20="u"))>INDEX({4.2.3};VERGLEICH(Tabelle1!$B2;{"Gruppe2"."Büro"."WE"};0))
Namen verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruß Sepp

Anzeige
AW: Bedingte Formatierung
23.11.2015 17:43:53
Simone
Hallo anbei das Beispiel.
irgendwie funktioniert es nicht.

Die Datei https://www.herber.de/bbs/user/101754.xlsx wurde aus Datenschutzgründen gelöscht


Die Abteilungen müssen separat sein.
LG Simone

Anzeige
AW: Bedingte Formatierung
23.11.2015 19:27:38
Sepp
Hallo Simone,
nur mit bedingter Formatierung.
https://www.herber.de/bbs/user/101756.xlsx
Gruß Sepp

Anzeige
AW: Bedingte Formatierung
23.11.2015 20:10:12
Simone
Hallo sepp,
kann man das auch mit der Gültigkeit machen?
Möchte gerne das da eine Meldung kommt.
lg simone

AW: Bedingte Formatierung
23.11.2015 20:13:13
Sepp
Hallo Simone,
die Gültigkeit will mit dieser Formel nicht arbeiten :-((
Anbei ein Beispiel per VBA mit Meldung!
https://www.herber.de/bbs/user/101757.xlsm
Da du das Ganze sicher für mehrere Blätter brauchst, müsste man das an deine Datei anpassen.
Dazu sollte man wissen, in welchen Blättern es laufen soll? ("Januar" bis "Dezember"?)
Gruß Sepp

Anzeige
AW: Bedingte Formatierung
23.11.2015 20:29:12
Simone
Ja wäre Januar bis Dezember

AW: Bedingte Formatierung
23.11.2015 20:55:36
Simone
Hallo
super danke.
Wie heißt den der Makro code.
Damit ich ihn in meiner original Liste mit einfügen kann?

sorry
23.11.2015 21:00:41
Simone
hab es gefunden. hab falsch geschaut.
Wenn der Chef trotzdem eine Ausnahme macht, kann man trotzdem einen dazu tun.
Geht das irgendwie?
Mit einer Sondermeldung?
LG simone

Anzeige
AW: sorry
23.11.2015 21:05:42
Sepp
Hallo Simone,
klar, geht auch.
' **********************************************************************
' Modul: DieseArbeitsmappe Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim lngC As Long, varRet As Variant

On Error GoTo Errorhandler

Select Case Sh.Name
  Case "Januar", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("C3:AG87")) Is Nothing Then
      With Target(1, 1)
        lngC = Application.CountIfs(Range(Cells(3, 2), Cells(87, 2)), Cells(.Row, 2), Range(Cells(3, .Column), Cells(87, .Column)), "u")
        varRet = Application.Match(Cells(.Row, 2), Range("AI3:AI8"), 0)
        If IsNumeric(varRet) Then
          If lngC > Range("AI3:AI8").Cells(varRet, 1).Offset(0, 1) Then
            If MsgBox("Keine Eintrag an diesem Tag für diese Gruppe möglich!" & vbLf & vbLf & _
              "Urlaub trozdem eintragen?", 308) = 7 Then
              
              Application.Undo
            End If
          End If
        End If
      End With
    End If
  Case Else
End Select

Errorhandler:
Application.EnableEvents = True
End Sub

Gruß Sepp

Anzeige
super, danke dir :')
23.11.2015 21:14:26
Simone
.

Nachgefragt
24.11.2015 07:09:19
Simone
Hallo Sepp,
wie muss ich den Code ändern,
wenn ich
Spalte A Personalnummer
Spalte B Name
Spalte C Abteilung
Spalte D Kinder
Spalte E Resturlaub
Und ab Spalte F bis Spalte AJ 1 bis 31 gehen.
Ab zeile 10 würde der Name losgehen.(Zeile 1 bis 9 stehen andere Daten ( Logo usw.))
Die Daten welche Abteilung und wieviele steht in jeden ( Januar bis Dezember) Tabellenblatt immer in AT und AU drin
AT2=WE----------AU=2
AT3=Gruppe1-----AU=5
AT4=Gruppe2-----AU=4
AT5=Büro--------AU=2
AT6=Büro Lager--AU=3
AT7=Lager-------AU=2
Ich hoffe du kannst mir nocheinmal helfen
Dankeschön
Simone

Anzeige
AW: Nachgefragt
24.11.2015 18:40:44
Sepp
Hallo Simone,
das sollte so klappen.
' **********************************************************************
' Modul: DieseArbeitsmappe  Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim lngC As Long, varRet As Variant

On Error GoTo Errorhandler

Select Case Sh.Name
  Case "Januar", "Februar", "März", "April", "Mai", "Juni", "Juli", "August", "September", "Oktober", "November", "Dezember"
    Application.EnableEvents = False
    
    If Not Intersect(Target, Range("F10:AJ94")) Is Nothing Then
      With Target(1, 1)
        lngC = Application.CountIfs(Range(Cells(10, 3), Cells(94, 3)), Cells(.Row, 3), Range(Cells(10, .Column), Cells(94, .Column)), "u")
        varRet = Application.Match(Cells(.Row, 3), Range("AT3:AT8"), 0)
        If IsNumeric(varRet) Then
          If lngC > Range("AT3:AT8").Cells(varRet, 1).Offset(0, 1) Then
            If MsgBox("Keine Eintrag an diesem Tag für diese Gruppe möglich!" & vbLf & vbLf & _
              "Urlaub trozdem eintragen?", 308) = 7 Then
              
              Application.Undo
            End If
          End If
        End If
      End With
    End If
  Case Else
End Select

Errorhandler:
Application.EnableEvents = True
End Sub

Gruß Sepp

Anzeige
;

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige