Formule avec données dans un autre classeur

[bourbonica974]

Bonjour

J'utilise un tableau qui fait appel à certaines données qui se trouvent dans un autre classeur. Lors de la création de la formule avec l'autre classeur ouvert il n'y a pas de probleme. J'enregistre le tout et je ferme. Dès que j'ouvre mon tableau, les valeurs dans mon tableau affiche #VALEUR!
En fait il faut que l'autre classeur soit ouvert pour que ca fonctionne. Il n'y a pas un moyen pour qu'il ne soit pas necessaire d'ouvrir l'autre classeur ?
merci

 

[bourbonica974]

Re : Formule avec données dans un autre classeur

ou une macro qui ouvrirait l'autre classeur

 

[job75]

Re : Formule avec données dans un autre classeur

Bonjour bourbonica974,

J'utilise un tableau qui fait appel à certaines données qui se trouvent dans un autre classeur. Lors de la création de la formule avec l'autre classeur ouvert il n'y a pas de probleme.

Il faudrait nous donner la ou les formules de liaison utilisées.

Et mieux, joindre les 2 classeurs, allégés sans données confidentielles.

A+

 

[bourbonica974]

Re : Formule avec données dans un autre classeur

Bonjour

Voici la formule. Impossible de joindre les 2 classeurs car ils sont tres volumineux!

=SI(SOMME.SI.ENS('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AD$1:$AD$65536;'H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AO$1:$AO$65536;$D$3;'H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AQ$1:$AQ$65536;$A8)>0;((C8/(SOMME.SI.ENS('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AD$1:$AD$65536;'H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AO$1:$AO$65536;$D$3;'H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AQ$1:$AQ$65536;$A8)))*180);0)

En esperant que la formules vous permettra de resoudre mon pb.

merci

 

[job75]

Re : Formule avec données dans un autre classeur

Bonjour bourbonica974,

SOMME.SI.ENS ne fonctionne pas sur un classeur fermé.

Utilisez SOMMEPROD qui n'a pas ce problème.

Et remplacez peut-être 65536 par la hauteur utile pour éviter des calculs trop longs.

A+

 

[job75]

Re : Formule avec données dans un autre classeur

Re,

En appliquant ce que j'ai dit au post #5 on obtient :

=SI(SOMMEPROD('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AD1:$AD10000;('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AO1:$AO10000=$D$3)*('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AQ1:$AQ10000=$A8))>0;180*C8/SOMMEPROD('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AD1:$AD10000;('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AO1:$AO10000=$D$3)*('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AQ1:$AQ10000=$A8));0)

Sur Excel 2007 et versions suivantes on peut utiliser SIERREUR :

=SIERREUR(180*C8/SOMMEPROD('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AD1:$AD10000;('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AO1:$AO10000=$D$3)*('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xls]Données'!$AQ1:$AQ10000=$A8));0)

Adapter les 10000 si nécessaire.

A+

 

[bourbonica974]

Re : Formule avec données dans un autre classeur

Bonjour Job75

J'ai remplacé SOMME.SI.ENS par SOMMEPROD j'ai un message erreur : #N/A.
Mon tableau contient 55000 lignes !!

 

[bourbonica974]

Re : Formule avec données dans un autre classeur

Ci joint un 1er fichier que j'ai dû supprimer des lignes.

 

[bourbonica974]

Re : Formule avec données dans un autre classeur

impossible de reduire la taille de l'autre fichier (1.60mo), comment est ce qu'on pourrait faire ?

 

[job75]

Re : Formule avec données dans un autre classeur

Bonjour bourbonica974, le forum,

Dans votre fichier (.xlsx) vous avez des #N/A en colonne AQ de la feuille "Données".

Vous pouvez dans cette colonne les éliminer en utilisant =SIERREUR(xxxxx;"")

Vous pouvez aussi, c'est plus simple, ajouter ESTNUM(LN(xxxx)) dans la formule avec SOMMEPROD :

=SI(SOMMEPROD('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AD1:$AD60000;('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AO1:$AO60000=$D$3)*ESTNUM(LN('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AQ1:$AQ60000=$A8)))>0;180*C8/SOMMEPROD('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AD1:$AD60000;('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AO1:$AO60000=$D$3)*ESTNUM(LN('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AQ1:$AQ60000=$A8)));0)

qu'on peut écrire sur Excel 2007 :

=SIERREUR(180*C8/SOMMEPROD('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AD1:$AD60000;('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AO1:$AO60000=$D$3)*ESTNUM(LN('H:\CA et Marge\CA\[CA CUMULE 2014 2015.xlsx]Données'!$AQ1:$AQ60000=$A8)));0)

Bonne journée et A+

 

[job75]

Re : Formule avec données dans un autre classeur

Re,

Attention aussi, en colonne AO vous avez des textes (à cause de GAUCHE) et pas des nombres.

Donc pour que la formule SOMMEPROD renvoie le bon résultat il faut que D3 soit aussi du texte.

Si vous renseignez manuellement cette cellule mettez-la au format Texte.

A+

 

[bourbonica974]

Re : Formule avec données dans un autre classeur

merci pour votre job75

j'ai le choix entre les 2 formules decrites ci dessous, c'est ca.

Par contre quand je fais un copié collé de votre formule, j'ai ce message (cf doc joint)

 

[bourbonica974]

Re : Formule avec données dans un autre classeur

Re,

Attention aussi, en colonne AO vous avez des textes (à cause de GAUCHE) et pas des nombres.

Donc pour que la formule SOMMEPROD renvoie le bon résultat il faut que D3 soit aussi du texte.

Si vous renseignez manuellement cette cellule mettez-la au format Texte.

A+

en Fait pour D3 c'est une liste deroulante, dans ce cas je fais un clic droit /format cellule/ et je choisi Texte ?

Merci encore une fois

 

[job75]

Re : Formule avec données dans un autre classeur

Par contre quand je fais un copié collé de votre formule, j'ai ce message (cf doc joint)

Il est clair ce message non ???

 

[bourbonica974]

Re : Formule avec données dans un autre classeur

Pour moi non!

"selectionner la feuille à partir de laquelle mettre à jour les valeur" je choisis laquelle ?
je clique sur n'importe laquelle, par ex la feuil1 et valeur = 0