Formule SOMEPROD(SOMME.SI(INDIRECT) renvoi REF
Bonjour,
Je rencontre un soucis avec une formule qui me renvoi comme résultat REF.
Ci-dessous la formule :
=SOMMEPROD(SOMME.SI(INDIRECT("'"&'FLEET INFORMATION'!$B$3:$B$61&"'!$A$2");'FLEET INFORMATION'!$B$3:$B$61;INDIRECT("'"&'FLEET INFORMATION'!$B$3:$B$61&"'!B5")))
j'ai une feuille nommée FLEET INFORMATION en colonne B qui contient des numéros de parc matériel qui change en fonction du parc actuel et chaque cellule de cet colonne renomme une feuille avec un numéro de parc dans ce classeur.(EX : MI 001, MI 002 etc...)
Cet formule me permet d'aller chercher un résultat dans une cellule précise (B5) en fonction du nom de la feuille contenu dans FLEET INFORMATION colonne B ce qui m'évite d'indiquer le nom de la feuille dans la formule vu que ces numéros de parc sont ajouté ou supprimé au cours de l'année. Le problème vu que certain numéro de parc sont supprimé avec la feuille correspondante la formule ne trouvant aucun résultat me renvoi REF.
Je souhaiterai savoir comment faire mème si la feuille n'est plus existante pour que la formule ne prenne en compte seulement que celle qui existe.
Ci-joint le fichier
Cordialement
Rudolph
Bonsoir,
en regardant votre formule, les INDIRECT renvoient la valeur : MI 002!$A$2
Hors MI 002 existe bien, du coup je ne comprend plus votre demande...
Ensuite vous utilisez un SOMME.SI avec des plages de une seule ligne....
Le mieux serait de reformuler ce que vous voulez, non ? Car un Sommeprod pour une ligne de test... plus un SOMME.SI et le tout arrosé d'INDIRECT....
Après si votre formule fonctionne et que ce n'est que le problème de l'erreur qui pose problème, alors
faite un : =SIERREUR(votre formule ave le Sommeprod;"")
Si une erreur on affiche le double guillemet, donc rien, sinon on affiche le résultat.
@ bientôt
LouReeD
Bonjour,
Ce que je souhaite est d'obtenir la somme de MI 001, MI 002 même si la feuille de MI 003 n'existe pas et aussi que lorsque dans la feuille FLEET INFORMATION colonne B si une cellule est vide dans la zone de recherche de la formule même chose que ça n’empêche pas la somme de MI 001, MI 002 car des que j'ai une feuille qui n'existe pas ou une cellule vide la formule me renvoie REF.
A voir si cela reste possible car lorsque j'utilise SI.ERREUR vu que j'ai une feuille qui n'existe pas ou une cellule vide le résultat est "".
Cordialement
Rudolph
Je commence à comprendre...
Donc sur votre fichier, en ajoutant une feuille MI 003 avec des valeurs égales à la feuille MI 002 sauf qu'en cellule A2 j'ai mis MI 072,
il n'y a plus le message d'erreur mais juste -
Donc sans parler d'erreur du à une feuille inexistante, déjà je crois que la formule ne fait pas ce que vous attendez, ne devrait-elle pas afficher 550 (110 + 220 + 220) ?
@ bientôt
LouReeD
bonjour
une tentative pour arriver a faire marcher
cordialement
Re,
un mélange de la réponse de tulipe_4 et un peu de ma nouvelle réponse.
Le fichier passe en XLSM car j'utilise une fonction "Macro" Excel 4.0 dans la formule d'un nom du classeur :
Onglets, qui me permet d'identifier la liste des onglets.
Ensuite grâce à la formule en colonne A de la feuille "FLEET Information" qui se répète vers le bas, la liste_onglet est mise à jour en fonction du résultat de cette formule.
Dans la deuxième feuille, j'ai repris la formule de tulipe_4 mais du coup elle marche si on supprime ou ajoute une feuille.
fichier : supprimé par LouReeD
@ bientôt
LouReeD
bonjour Lou reed
en analysant la formule et, meme en la refaisant ,j'ai remarqué que Indirect refusait obstinement d'afficher à l'assistant fonction: M 001 ,normalement c'est toujours le premier de la liste , ce n'est pas le cas ,???????
ce type de formule ne tolère pas les vides; ni tout ce qui peut engendrer une erreur
cordialement
Là je ne comprend pas...
Mon fichier somme bien ce qu'il faut, si on efface un onglet "M XXX" alors la liste se met à jour sans "trou" et la formule en feuille 2 se met à jour également sans erreur...
Si j'ajoute un onglet et que je lui donne un nom quelconque, il s'ajoute à la liste "sans trou" et il est pris en compte dans la "liste_onglets", donc dans la formule, une fois une donnée entrée en $B$5, la somme se met à jour, non ?
Ca ne marche pas chez vous ? Chez moi ça fonctionne... Peut-être que LIRE.Classeur n'existe pas chez vous...
@ bientôt
LouReeD
re
rassure toi je je parlais pas de ton fichier
non, je ne dispose pas de ces fonctions , je me satisfais du pack de base , à tort
cordialement
Bonsoir,
vu sur le net :
Introduction aux macros XL4
Donc vous devriez l'avoir sur votre monture d'Excel même s'il est de base...XL4 désigne la version 4 d’Excel. L’expression macro XL4 est utilisée pour désigner les macros écrites avec le langage macro intégré à Excel dès la première version.
Ce langage est dorénavant considéré comme obsolète ou presque, car remplacé par VBA. Cependant, ces macros présentent encore un intérêt majeur dans certains cas
Cela n'a rien avoir avec "MoreFunction" et le pack 65 de ce site-ci !
@ bientôt
LouReeD
bonsoir
ben ...............j'avoue ne meme pas savoir comment y accèder , je suis l'archetype de l'autodidacte qui ne progresse qu'a tatons et plantades ......
cordialement
On est deux alors !
@ bientôt
LouReeD
Bonjour,
Désolé pour ce retour tardif.
Je vous remercie pour le temps passé sur ce sujet.
Vos réponse sont inintéressante mais hélas il y a un soucis car dans ma feuille FLEET INFORMATION colonne B dans le vrai fichier j'ai une macro qui utilise ces cellule lorsque je saisie un numéro de parc il me suffit de faire un double clic gauche sur cet cellule pour créer une nouvelle feuille avec un tableau type ou un clic droit pour la supprimer.
voila pourquoi il faut que j'utilise la formule indirecte en faisant la recherche dans cet colonne vu que ces numéros de parc sont en constante évolution.
Voir le fichier ci-joint
Cordialement
Rudolph
Bonjour,
si le seul problème vient du faite de cliquer gauche ou droit pour créer une feuille ou bien la supprimer, alors...
ma version va très bien... Il suffit de laisser sur la colonne B la liste des formules qui "répertorie" les feuilles existante et de mettre un nouveau bouton "créer feuille" qui lancera la macro de création avec un inputbox qui demandera le nom de cette feuille.
Pour la suppression il suffira de faire un clic droit directement sur l'onglet de la feuille est de faire supprimer.
Dans les deux cas la liste des feuille en colonne B se mettra à jour, donc les formules "indirect" seront elles aussi à jour.
Vous me direz : oui mais pour la création je n'ai pas de nom à donner sur mon fichier...
Disons que SI, car le nom il a bien fallut le mettre dans la colonne B, et comme lors d'une suppression le nom est effacé, et comme vous dites que ça n'arrête pas de changer, la colonne B doit être régulièrement renseignée, non ?
Si j'ai un peu de temps je vous fait la modification.
@ bientôt
LouReeD
Bonjour,
Exacte.
Je suis preneur pour votre solution.
Merci encore pour votre temps.
Cordialement
Rudolph
Bonsoir,
voilà ma dernière proposition, à vous d'adapter les formules si besoin...
Le fichier :
Le principe est simple :
un bouton pour "Ajouter", répondre en entrant le nom de la feuille, vérification de ce nom : je suis partis sur les principes suivants :
- commence par MI_ (_ pour signifier un espace)
- suivi de trois chiffres
- un total de 6 caractères
- un nom différent des noms de feuille du classeur
Si le nom correspond pas, alors on sort de la procédure en le signifiant.
Une fois créée les feuilles sont triées par ordre alphabétique, ce qui fait qu'en colonne B les noms sont également triés.
Pour supprimer il suffit de faire un clic droit sur le nom des feuilles de la colonnes B.
Après suppression, les "trous" sont bouchés.
Actuellement, les formules de la colonne B vont jusqu'en ligne 34, si cela ne suffit pas il suffit de copier/coller, ou bien tirer vers le bas pour la recopie des formules afin d'atteindre le nombre voulu.
@ bientôt
LouReeD
Toc, toc, toc !!!
Qui est là ?
@ bientôt
LouReeD
Bonjour,
Votre solution est parfaite !
Merci beaucoup pour votre aide.
Cordialement
Bonsoir,
merci @ vous pour votre retour.
@ bientôt
LouReeD