XL 2013 Mise en forme conditionnelle et index/equiv

[Ascali]

Bonjour à tous,

Ci joint mon fichier commencé.
J'ai besoin d'aide sur 2 points:
- le point de départ est le nom de ma feuille "2019" qui génère l'année "2019" en D1. J'ai une feuille avec les jours fériés de 2019 à 2031, je souhaiterais que la mise en forme conditionnelle (orange) pour les jours fériés soit associée à la cellule D1 pour que quand je créé une nouvelle feuille 2020, la mise en forme s'adapte à la nouvelle année.
- Idem pour la mise en forme conditionnelle jaune des anniversaires PV (feuille PV)
- en ligne 8, j'ai une formule qui me renvoie le nom du PV (PV1; PV2....) associé à la date d'anniversaire mais je n'arrive pas à faire une formule basée sur l'année en cellule D1 pour qu'elle se fasse quand je duplique ma feuille 2019 pour l'année 2020.
J'espère que je suis quand même clair dans mes explications.
Pouvez vous m'aider (sachant que je ne sais pas faire de VBA)?

Merci par avance.

 

[chris]

Bonjour

Voir
- le nom défini an
- les formules de MFC jaune et brique
- les formules en ligne 8

Je n'ai traité que le 1er bloc mais la copie en 2021 fonctionne

 

[Jacky67]

Bonjour à tous,

Ci joint mon fichier commencé.
J'ai besoin d'aide sur 2 points:
- le point de départ est le nom de ma feuille "2019" qui génère l'année "2019" en D1. J'ai une feuille avec les jours fériés de 2019 à 2031, je souhaiterais que la mise en forme conditionnelle (orange) pour les jours fériés soit associée à la cellule D1 pour que quand je créé une nouvelle feuille 2020, la mise en forme s'adapte à la nouvelle année.
- Idem pour la mise en forme conditionnelle jaune des anniversaires PV (feuille PV)
- en ligne 8, j'ai une formule qui me renvoie le nom du PV (PV1; PV2....) associé à la date d'anniversaire mais je n'arrive pas à faire une formule basée sur l'année en cellule D1 pour qu'elle se fasse quand je duplique ma feuille 2019 pour l'année 2020.
J'espère que je suis quand même clair dans mes explications.
Pouvez vous m'aider (sachant que je ne sais pas faire de VBA)?

Merci par avance.

Bonjour,
Une proposition en PJ
Avec modification du calcul des jours fériés fixes et des anniversaires.
L'année se met à jour au changement de feuille si celle-ci est numérique avec un peu de vba dans le thisworkbook
3 plages nommées "An " pour l'année , "FerieFixe" pour les jours fériés fixe, et "Anni" nommée dynamiquement pour les anniversaires
Orange pour les fixes, vert pour les anniversaires.
Il reste à mettre de l'ordre dans les MFCs…..

Bon courage

 

[Ascali]

Bonjour,
Déjà merci pour vos 2 réponses.
Je vais essayer de comprendre et d'analyser ce que vous avez fait.
Je reviens vers vous dès que je peux.
Pour la réponse de Chris, je ne trouve pas ce qui est nommé par "an".
Il y a quand même la partie VBA de Jacky67 que je ne comprend pas, si possible, y a-t-il des solutions sans VBA svp.
Encore merci.

 

[chris]

RE

Formules, Gestionnaire de noms : tu verras à quoi correspond an

Aucun VBA dans ma solution...

 

[Ascali]

Bonjour Chris,
Oui pas de VBA dans ta solution.
Après analyse, je ne comprend pas tout....
- le nom "an" est défini comme cela =!$D$1*1
le point d'exclamation seul veut-il bien dire cellule d1 de la feuille affichée?
pourquoi ce *1?
je ne comprend pas la formule de la mfc sur les jours fériés, les critères de la fonction decaler.
Peux tu m'expliquer dans le détail?
Pourquoi a-t-on une plage de valeur sur le premier paramètre qui est une référence selon excel?
Merci d'avance pour ton aide

 

[chris]

RE

!$D$1 est le cellule D1 de la feuille active, donc est 2019 sur la feuille 2019 et 2020 sur la feuille 2020

*1 parce que tes cellules D1 sont en texte et qu'il faut donc les convertir en numérique

DECALER permet de se positionner sur la bonne colonne de JF en se décalant horizontalement vers l'année (an) concernée.

 

[Ascali]

Merci pour le retour, j'ai adapté toutes les formules pour les caler sur l'ensemble de l'année 2019. Tout fonctionne, sauf que les mfc jour férié et pv s'activent sur les jours où les mois sont plus court (fev, avril, juin....).
Peux tu m'aider encore une fois sur ce point ?

 

[chris]

RE

Sur le fichier de jeudi mes MFC jaunes et brique peuvent être copiées sur tous les mois en modifiant à chaque fois A$4 pour A$12 puis A$20 etc...

Tu dis que tu a modifié mais tu ne dis pas quoi et comment...

 

[Ascali]

Bonjour,
désolé pour le manque de précisions. Oui j'ai adapté les mfc comme tu l'a dit.
Je te joins le fichier. Les mois qui n'ont pas 31 jours, ont les mfc qui s'activent sur les derniers jours.
C'est le dernier point pour le moment qu'il me manque pour finaliser le fichier.
Merci d'avance

 

[chris]

RE

Forcément si tu mets 50 à la place de 11, cela prend des lignes vides qui sont donc égales aux cellules dates vides !

 

[Ascali]

Bonsoir,
Ah d'accord, en fait j'ai mis 50 dans la mfc car la liste des pv est amenée à s'allonger de temps en temps. Je pensais donc prendre en compte les futures lignes à venir. Comment faut-il procéder?
En parallèle, peux tu m'expliquer en détail la mfc en question car j'ai vraiment un problème avec la fonction décaler (ses attributs)?
Encore désolé de prendre autant de ton temps.
Merci d'avance

 

[chris]

Re

11 c'est le nombre de Jours fériés chaque année

Pour les PV : on peut mettre

=NB.SI(DECALER(PV!$A$2;0;EQUIV(An;PV!$1:$1;0)-1;NBVAL(PV!$A:$A)-1;1);E$4)

De façon à suivre le nombre de lignes de pV

 

[Ascali]

Bonjour Chris,
J'avais un peu laissé tomber mon fichier, je m'y suis remis, et avec toute l'aide que tu m'as fourni, je suis arrivé à mes fins.
Donc merci beaucoup.
Bonne journée