XL 2019 Problème formule SOMMEPROD & INDEX

[Laliepomm]

Bonjour,
Je viens chercher vos connaissances.
J'ai un onglet qui liste les dates d'absence (maladie, congé) du personnel (reporting).
ensuite j'insère ces dates dans un onglet (Follow-up) reportant ces dates dans une frise chronologique.
J'ai trouvé une formule sommeprod et index puis l'ai adapté à mon fichier.
Mais, il me prend comme "code", celui se trouvant sur la cellule du dessous de la ligne à reporter.
Ex: pour George de la Jungle Z: code = V pour la période du 5/02 au 22/03 dans l'onglet reporting, donc je devrais retrouver les données de la cellule H4, mais dans la frise, le code reporté est "Z" (ziekte) = cellule H5 = correspondant à Edward aux mains d argent Y du 02/01 au 22/02.

J'ai tout essayé mais je n'arrive pas à trouver où se situe l'erreur dans la formule

D'avance merci pour votre support.

vous trouverez ci-joint le fichier.
(note: si jamais vous avez une astuce en vba, je prends aussi)

Cordialement Lalie

Note: la partie de la formule qui me pose problème se trouve dans les cellules de l'onglet "follow up":
(excel en néerlandais sur ce PC, mais si vous téléchargez le fichier vous verrez la formule en français)

ALS(X.ZOEKEN(CR$8;Calendar_ferie[Date];Calendar_ferie[Note];"";0)="F";"F";ALS(OF(CR$9="Zo";CR$9="Za");"";ALS(SOMPRODUCT((Report[wkn]=$A72)*(CR$8>=Report[Start datum])*(CR$8<=Report[Eind datum]))>0;INDEX(Report[Code];MIN(ALS((Report[wkn]=$A72)*(CR$8>=Report[Start datum])*(CR$8<=Report[Eind datum]);RIJ(Report[Code])))-1);"")))

 

[djidji59430]

Bonjour à tous,

A mon avis, ça ne peut pas marcher:
tu étires ta formule a droite et tes plages ne sont pas figées (elles ne sont pas en references absolues) Tu sautes d'une colonne

en e10 :

=SI(RECHERCHEX(E$8;Calendar_ferie[Date];Calendar_ferie[Note];"";0)="F";"F";SI(OU(E$9="Zo";E$9="Za");"";SI(SOMMEPROD((Report[wkn]=[@[Name of employee]])*(E$8>=Report[Start datum])*(E$8<=Report[Eind datum]))>0;INDEX(Report[Code];MIN(SI((Report[wkn]=[@[Name of employee]])*(E$8>=Report[Start datum])*(E$8<=Report[Eind datum]);LIGNE(Report[Code])))-2);"")))

en e11

=SI(RECHERCHEX(F$8;Calendar_ferie[Jour férié];Calendar_ferie[Date];"";0)="F";"F";SI(OU(F$9="Zo";F$9="Za");"";SI(SOMMEPROD((Report[januari]=[@Contract])*(F$8>=Report[Eind datum])*(F$8<=Report[Calendar Dagen]))>0;INDEX(Report[wkn];MIN(SI((Report[januari]=[@Contract])*(F$8>=Report[Eind datum])*(F$8<=Report[Calendar Dagen]);LIGNE(Report[wkn])))-2);"")))

a partir de index, tes colonnes de criteres changent. il fait les mettre en references absolues

Crdlmt

 

[Laliepomm]

Bonjour à tous,

A mon avis, ça ne peut pas marcher:
tu étires ta formule a droite et tes plages ne sont pas figées (elles ne sont pas en references absolues) Tu sautes d'une colonne

en e10 :

=SI(RECHERCHEX(E$8;Calendar_ferie[Date];Calendar_ferie[Note];"";0)="F";"F";SI(OU(E$9="Zo";E$9="Za");"";SI(SOMMEPROD((Report[wkn]=[@[Name of employee]])*(E$8>=Report[Start datum])*(E$8<=Report[Eind datum]))>0;INDEX(Report[Code];MIN(SI((Report[wkn]=[@[Name of employee]])*(E$8>=Report[Start datum])*(E$8<=Report[Eind datum]);LIGNE(Report[Code])))-2);"")))

en e11

=SI(RECHERCHEX(F$8;Calendar_ferie[Jour férié];Calendar_ferie[Date];"";0)="F";"F";SI(OU(F$9="Zo";F$9="Za");"";SI(SOMMEPROD((Report[januari]=[@Contract])*(F$8>=Report[Eind datum])*(F$8<=Report[Calendar Dagen]))>0;INDEX(Report[wkn];MIN(SI((Report[januari]=[@Contract])*(F$8>=Report[Eind datum])*(F$8<=Report[Calendar Dagen]);LIGNE(Report[wkn])))-2);"")))

a partir de index, tes colonnes de criteres changent. il fait les mettre en references absolues

Crdlmt

Bonjour,
je ne comprends pas ton retour.
J'ai fait attention à ce que les formules soient identiques à l'exception de la date "ligne DAY" - 8. Non en étirant la formule mais en la copiant.
Mes formules sont identiques en E10 et E11, en F10: seule la colonne pour le "day" change - comme suit :

E10 :

ALS(X.ZOEKEN(E$8;Calendar_ferie[Date];Calendar_ferie[Note];"";0)="F";"F";ALS(OF(E$9="Zo";E$9="Za");"";ALS(SOMPRODUCT((Report[wkn]=[@[Name of employee]])*(E$8>=Report[Start datum])*(E$8<=Report[Eind datum]))>0;INDEX(Report[Code];MIN(ALS((Report[wkn]=[@[Name of employee]])*(E$8>=Report[Start datum])*(E$8<=Report[Eind datum]);RIJ(Report[Code])))-1);"")))

E11 :

ALS(X.ZOEKEN(E$8;Calendar_ferie[Date];Calendar_ferie[Note];"";0)="F";"F";ALS(OF(E$9="Zo";E$9="Za");"";ALS(SOMPRODUCT((Report[wkn]=[@[Name of employee]])*(E$8>=Report[Start datum])*(E$8<=Report[Eind datum]))>0;INDEX(Report[Code];MIN(ALS((Report[wkn]=[@[Name of employee]])*(E$8>=Report[Start datum])*(E$8<=Report[Eind datum]);RIJ(Report[Code])))-1);"")))

F10:

ALS(X.ZOEKEN(F$8;Calendar_ferie[Date];Calendar_ferie[Note];"";0)="F";"F";ALS(OF(F$9="Zo";F$9="Za");"";ALS(SOMPRODUCT((Report[wkn]=[@[Name of employee]])*(F$8>=Report[Start datum])*(F$8<=Report[Eind datum]))>0;INDEX(Report[Code];MIN(ALS((Report[wkn]=[@[Name of employee]])*(F$8>=Report[Start datum])*(F$8<=Report[Eind datum]);RIJ(Report[Code])))-1);"")))

Ensuite, la formule INDEX prend la bonne colonne, mais malheureusement pas la bonne ligne 🙁.
Dans le tableau5 (tabel5) : en E11 - il reporte l'information correspondante à la cellule Reporting H6 au lieu de Reporting H5.

Avez-vous une solution? ou bien savez-vous où se situe le problème de la ligne?

D'avance merci

Slts

lalie

 

[st007]

Bonjour, pourquoi index -1 ligne ?

 

[Laliepomm]

Bonjour, pourquoi index -1 ligne ?

Bonjour, c'était dans la formule que j'avais trouvé. J'ai essayé de l'enlever, mais cela ne résoud pas le problème pour toutes les lignes.

 

[djidji59430]

tu dois mettre -2
en effet Report

) commence en H3 ==>RIJ(Report[Code]))) doit commencer par 1 (le premier element de l'index) et la il commence en 3 donc RIJ(Report[Code])-2))

pour ma premiere reponse, j'avais du etirer moi meme la formule, et je n'ai pas fait attention !


Crdlmt

 

[Laliepomm]

Merci. Après avoir testé, ça semble fonctionner 🙂