Bonjour/bonsoir... (il y a une heure dans la nuit où je ne sais jamais quel mot choisir...)
Voilà ton fichier un peu modifié https://www.cjoint.com/c/JAhduH38WBA avec quelques commentaires ci-dessous.
Validation des saisies
Avant de commencer la moindre formule, pour être certain qu'une faute de frappe ne viendra pas fausser nos calculs, j'ai utilisé la fonction de Validation des données en mettant partout...
8h - 15h (par exemple)
...sans espace entre le chiffre et la lettre H et avec un tiret entouré d'espaces au milieu comme séparatif. C'est indispensable pour éviter les problèmes. Du coup, la saisie est simplifiée puisqu'on a une liste déroulante dans chaque cellule.
J'ai mis cette liste dans la plage Q3:Q8 mais à la limite, on pouvait utiliser la plage A15:A20
Formule par colonne
On doit certainement réussir avec une formule unique pour faire l'ensemble du tableau mais il me semble que la mettre au point passera plus de temps que de faire une formule par colonne légèrement modifiée à chaque fois. Bien entendu, avec 3 fois... 10 fois... 20 fois plus de colonnes, mon choix s'inverserait.
Donc en B15, je tape la formule
=INDEX($B$3:$G$3;1;EQUIV($A15;$B$4:$G$4;0))
La fonction EQUIV va regarder la valeur contenue dans $A15 (rappel, quand je copie cette formule vers le bas ou vers la droite, le A reste toujours le A mais le 15 se transforme en 16 17 18 etc) et cherche dans la plage $B$4 à $G$4 l'exacte correspondance.
Si la formule ne trouve pas, elle renvoie la valeur #NA et si elle trouve, elle renvoie la position dans la plage $B$4:$G$4
En l’occurrence, la valeur 8h - 15h se trouve dans la première colonne de la plage $B$4:$G$4
Je recherche donc dans la plage $B$3:$G$3 qui contient les nom à la ligne 1 et à la colonne que l'on vient de trouver grâce à EQUIV
Le résultat est STEPHANIE, le contenu de B3
Je copie cette formule de B15 à B20
En B17 j'ai un vide qui me donne comme résultat #NA mais on va le gérer plus tard.
Pour la colonne suivante, la colonne C, je copie la formule que j'adapte légèrement
=INDEX($B$3:$G$3;1;EQUIV($A15;$B$5:$G$5;0))
Dans la fonction EQUIV au lieu de regarder en ligne 4 je regarde dans la ligne 5
Et ainsi de suite pour le reste du tableau
En colonne D je regarde la ligne 6
=INDEX($B$3:$G$3;1;EQUIV($A15;$B$6:$G$6;0))
En colonne E je regarde la ligne 7
=INDEX($B$3:$G$3;1;EQUIV($A15;$B$7:$G$7;0))
En colonne F je regarde la ligne 8
=INDEX($B$3:$G$3;1;EQUIV($A15;$B$8:$G$8;0))
Gestion des #NA
La cerise sur le gâteau comme énoncé dans la question.
J'aurais pu faire une formule plus complexe en ajoutant quelque chose du genre
=SI(NON(ESTNA(bla bla bla...
...mais je l'ai joué à la méthode feignasse étant donné qu'on doit colorer ces cellules avec un fond rouge, je fais une mise en forme conditionnelle avec du texte rouge sur fond rouge et le message d'erreur est invisible. Attention, si on imprime avec l'option noir et blanc, #NA apparaît à l'impression.
Je sélectionne la plage $B$15:$F$20 et j'ajoute une règle avec l'option Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué
La formule est
=ESTNA(B15)
Attention à bien supprimer les $ qu'Excel met par défaut et comme format, je mets le texte et le fond rouges.
Mes cerises sur le gâteau
Ce n'est pas un clafoutis mais j'ai ajouté quelques petites cerises.
- Couleur automatique jaune pâle dans le tableau 1 partout où il y a la valeur RPLCT 7J
- Couleur automatique gris clair dans le tableau 1 dans toutes les cellules vides
- La saisie de la date de début en G1 permet de remplir les titres des deux tableaux en une seule saisie (je ne rentre pas dans le détail ici, ce n'est pas l'objet de la question)
En espérant que la réponse n'était pas trop longue.
Bon courage.
Posté le 
Posté le