Onglets et recherche de cellule dans EXCEL
jeudi 20 mai 2010, par François Le Ralle
Description du besoin
Chaque onglet contient une simulation annuelle de budget et s’appelle "Simulation 9999-07" où 9999 est l’année. 07 est fixe et signifie que mes calculs commencent tous début juillet (un choix personnel qui n’a aucun impact sur la méthode).
Il est important que le nom de l’onglet contienne le numéro de l’année, et qu’une cellule de l’onglet le contienne aussi.
Je veux chercher le solde dans l’onglet de l’année précédente et le reporter dans le nouvel onglet, sachant que je ne connais pas la cellule dans laquelle il se situe (le nombre de lignes et de colonnes peut varier), mais qu’il est repéré par une cellule adjacente qui contient le texte "Solde FLR LtA". La colonne de cette cellule adjacente a pour titre "Cumul Annuel", et la ligne ou ce situe ce titre ne varie pas, c’est important.
Ah oui, j’oubliais : je ne veux pas de macro, juste des formules.
Etapes
1- farbriquer le nom de l’onglet qui contient le solde de l’année précédente avec la ligne 2 dans laquelle se fera la recherche de la colonne. Je décrémente l’année contenu au début de la cellule A2 pour nommer l’onglet précédent :
A106 ="'Simulation " & TEXTE(CNUM((GAUCHE($A$2;4))-1);"standard") & "-07'!2:2"
2- Chercher la colonne dans laquelle se trouveront les textes à chercher et transformer son numéro en codes à lettres. Cette colonne a pour titre "Cumul Annuel" en ligne 2.
A103 =EQUIV("Cumul annuel";INDIRECT(A106;VRAI))
A104 =SI(ENT(A103/26)>0;CAR(ENT(A103/26)+64);"")&CAR(MOD(A103;26)+64)
3- Fixer la position de la colonne qui contient le solde (dans mon cas 2 places avant les libellés cherchés), et transformer son numéro en codes à lettres. :
A108 =A103-2
A109==SI(ENT(A108/26)>0;CAR(ENT(A108/26)+64);"")&CAR(MOD(A108;26)+64)
4- Créer le nom de l’onglet avec la colonne dans laquelle on va chercher le texte :
A111 ="'Simulation " & TEXTE(CNUM((GAUCHE($A$2;4))-1);"standard") & "-07'!" & A104 & ":" & A104
5- Recherche de la ligne qui contient le texte à chercher "Solde FLR LtA" :
A125 ==EQUIV("Solde FLR LtA";INDIRECT(A111;VRAI))
6. Créer la référence complète de la cellule dans laquelle se trouve le solde de l’année précédente (sur l’onglet précédent) :
A127 ="'Simulation " & TEXTE(CNUM((GAUCHE($A$2;4))-1);"standard") & "-07'!" & A109 & A125
7. Pour lier la simulation de cette année avec celle de l’année précédente, le solde de l’année précédente s’obtient par la formule suivante :
A129 =INDIRECT(A127;VRAI)
Et voilà. Quand on crée un nouvel onglet en recopiant le précédent, qu’on le nomme correctement et que l’on mentionne l’année dans la bonne cellule, les soldes se reportent automatiquement.
RSS 2.0