Excel formule hulp gevraagd

[Rob Storm]

Hallo,

Ik heb bijgevoegd een excelbestand met daarin een vakantielijst.

De bedoeling is dat op blad1 in cel B5 het totale aantal opgenomen uren komt te staan dat tot en met de datum in cel B1 is ingevoerd. De gegevens moeten dan weer uit blad vakantiedagen2007 gehaald worden. (datum=E, de namen staan op rij 1, en daaronder de reeds opgenomen dagen)

Ik heb geen idee hoe ik dit moet doen en hoop dat jullie mij kunnen helpen.

Alvast bedankt
Groetjes
Rob

 

[Demeter]

Rob,

Ter info:
Denk dat alle jusite namen nog in je sheet staan, deze kan je bter vervangen door Bogus namen. bv. Bert, Ernie, Pino, etc.
Tevens staan bij je eigenschappen van je file ook de bedrijfsnaam. Zo kan ik wel heel makkelijk kijken wie er bij jullie op de afdeling weg is i een bepaalde periode.

Ik zou in ieder geval de namen veranderen en je file hierna vervangen.

 

[Demeter]

Niet mijn sterkste punt (maar als je er niet aan begint kom je er nooit achter .

Probeer deze formules eens op blad1 in cel B5 en dan naar beneden door trekken:

=SOM(VERSCHUIVING('Vakantiedagen 2007'!$A$10:$A$374;;VERGELIJKEN(A5;'Vakantiedagen 2007'!$F$1:$AB$1;0)+4))

Denk dat er nog wel wat betere komen maar het eerste schot is er.

ps.
Vraag eens aan Martin of hij nog vakantie plannen heeft? LOL

 

[Rob Storm]

Helaas,

Hoi Ferenc,

Helaas werkt nog niet zoals bedoeld, gek genoeg komen niet de juiste waarden eruit als ik ga optellen, en als ik de datum in B1 wijzig zouden de waarden in B5 t/m 27 ook moeten wijzigen en dit gebeurt helaas nog niet. En ik zie zelf niet waar het probleem zit.......

Misschien moet ik nog maar eens een cursus nemen???

 

[Demeter]

Je kan het ook met horizontaal zoeken proberen.
Zet de volgende code in cel B5 van Blad1 en trek door naar beneden:

=HORIZ.ZOEKEN(A5;'Vakantiedagen 2007'!$F$1:$AB$2;2)

Hij neemt nu de uren over welke vermeld staan in regel 2 (Totaal opgenomen uren in 2007).

 

[JanJantje]

Beste Rob,

Ik heb het op de volgende manier opgelost.

in blad1
cel F5: 6
cel F6: 7

en doorvoeren tot F27

in blad1 B5 de volgende formule

=SOM(INDIRECT(ADRES(VERGELIJKEN('Vakantiedagen 2007'!$E$10;'Vakantiedagen 2007'!$E$10:$E$374;0)+9;F5;;;"Vakantiedagen 2007")):INDIRECT(ADRES(VERGELIJKEN($B$1;'Vakantiedagen 2007'!$E$10:$E$374;0)+9;F5;;;"Vakantiedagen 2007")))

eh voila

Edit:
een mogelijke oplossing zonder hulpkolom F:
dus in blad1 B5:

=SOM(INDIRECT(ADRES(VERGELIJKEN('Vakantiedagen 2007'!$E$10;'Vakantiedagen 2007'!$E$10:$E$374;0)+9;VERGELIJKEN(A5;$A$5:A5;0)+5;;;"Vakantiedagen 2007")):INDIRECT(ADRES(VERGELIJKEN($B$1;'Vakantiedagen 2007'!$E$10:$E$374;0)+9;VERGELIJKEN(A5;$A$5:A5;0)+5;;;"Vakantiedagen 2007")))

Jantje

 

[robdgr]

Gezien het kleine aantal namen, zou ik hem zo oplossen:

=SOM.ALS('Vakantiedagen 2007'!E11:E375;">="&DATUM(2007;1;1);'Vakantiedagen 2007'!F11:F375)-SOM.ALS('Vakantiedagen 2007'!E11:E375;">="&B1;'Vakantiedagen 2007'!F11:F375)

en dat dan even handmatig per naam.

Koninginnendag

? We hebben er nog steeds maar één

Waarom niet gewoon

=E10+1

in kolom E? Dan hoef je volgend jaar maar één cel te wijzigen.

Groet,

 

[Demeter]

Misschien kan hij nog efficienter maar hierbij een kleine VBA code voor achter je blad Personen (wat nu Blad1 heet).

Private Sub Worksheet_Activate()
Dim c As Range
Dim begincel, eindcel, datum As String
Dim laatsteregel As Long

Application.ScreenUpdating = False

    For Each c In Sheets("Personen").Range("A5:A27")
        If c <> "" Then
        
            With Worksheets("Vakantiedagen 2007")
                '****************Tot en met uren
                Set zoeknaam = .Range("F1:AB1").Find(c.Value, LookIn:=xlValues)
                begincel = Cells(10, zoeknaam.Column).Address
                laatsteregel = .Range("E65536").End(xlUp).Row + 1
                datum = Format(Sheets("Personen").Range("B1"), "Short Date")
                Set tot_datum = .Range("E10:E" & laatsteregel).Find(What:=CDate(datum), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                eindcel = Cells(tot_datum.Row, zoeknaam.Column).Address
                c.Offset(, 1) = Application.WorksheetFunction.Sum(.Range(begincel, eindcel))
                
                '****************Totaal aantal uren
                Set zoeknaam = .Range("F1:AB1").Find(c.Value, LookIn:=xlValues)
                eindcel = Cells(laatsteregel, zoeknaam.Column).Address
                c.Offset(, 2) = Application.WorksheetFunction.Sum(.Range(begincel, eindcel))
            End With
        End If
    Next

Application.ScreenUpdating = True

End Sub

 

[robdgr]

In bijgaand voorbeeld mijn eerdere suggesties uitgewerkt. Kijk ook even naar de functie

=HERHALING("tekst";10)

 

[Rob Storm]

Bedankt

Hallo allemaal,

Ben erg blij met alle oplossingen, ben voor de tweede oplossing van Jan Jantje gegaan deze werkt voor mij prima. maar erg leuk om te zien dat iedereen hier zo aan mee wil werken.

Zeer bedankt voor de moeite.

Vriendelijke groeten
Rob