SOLVED

Formules et fonction avec champ de nom

%3CLINGO-SUB%20id%3D%22lingo-sub-3027391%22%20slang%3D%22fr-FR%22%3EFormulas%20and%20function%20with%20name%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3027391%22%20slang%3D%22fr-FR%22%3E%3CP%3EGood%20morning%3C%2FP%3E%3CP%3EI%20have%20several%20excel%20files%20or%20I%20have%20several%20constants%20and%20several%20variables%3C%2FP%3E%3CP%3EI%20associated%20a%20name%20to%20the%20cell%20having%20constants%20so%20my%20cells%20F4%2C%20G4%2C%20H4%20is%20called%20gravity%2C%20magnet%2C%20force%3C%2FP%3E%3CP%3Ethen%20my%20variables%20are%20in%20the%20columns%20F7%2C%20F8%2C%20F9%20etc%20but%20also%20G7%2C%20G8%20etc%3C%2FP%3E%3CP%3Ein%20column%20E7%2C%20E8%2C%20etc%20in%20front%20of%20my%20variables%2C%20I%20write%20the%20force%20that%20is%20exerted%20on%20my%20variables%20either%3A%20gravity%2C%20magnet%2C%20force%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20able%20to%20make%20in%20the%20column%20H7%2C%20H8%2C%20H9%20etc%20in%20front%20of%20the%20variables%20and%20text%20giving%20which%20constant%20is%20applied%20the%20result%20of%20my%20formula%20without%20writing%20in%20the%20formula%20the%20number%20of%20the%20cell%20but%20rather%20by%20selecting%20it%20either%20F7%20*%20E7%20%3D%20result%3C%2FP%3E%3CP%3Ebut%20as%20E7%20is%20an%20Excel%20text%20comes%20out%20of%20me%20%23VALEUR%3C%2FP%3E%3CP%3Eneed%20to%20change%20the%20gravity%20text%20or%20then%20I%20put%20quotation%20marks%20or%20other%20to%20cell%20E7%3C%2FP%3E%3CP%3Etoy%20you%20can%20copy%20paste%20my%20formula%20on%20the%20whole%20page%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eolive%20tree%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3027391%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3027522%22%20slang%3D%22en-US%22%3ERe%3A%20Formules%20et%20fonction%20avec%20champ%20de%20nom%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3027522%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1236644%22%20target%3D%22_blank%22%3E%40oliviermouaze%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DE7%26amp%3B%22%20%22%26amp%3BF7*G7%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20what%20you%20want%20to%20do%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3027549%22%20slang%3D%22fr-FR%22%3ERe%3A%20Formulas%20and%20function%20with%20name%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3027549%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eunfortunatly%20not%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3027560%22%20slang%3D%22fr-FR%22%3ERe%3A%20Formulas%20and%20function%20with%20name%20field%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3027560%22%20slang%3D%22fr-FR%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1174419%22%20target%3D%22_blank%22%3E%40Quadruple_Pawn%3C%2FA%3E%20if%20this%20can%20help%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22test.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F331235i1ECABA9D985438D8%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22test.jpg%22%20alt%3D%22test.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3027694%22%20slang%3D%22en-US%22%3ERe%3A%20Formules%20et%20fonction%20avec%20champ%20de%20nom%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3027694%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1236644%22%20target%3D%22_blank%22%3E%40oliviermouaze%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(IF(E8%2F(C8*D8)%3DGravity%2C%22gravity%22%2CIF(E8%2F(C8*D8)%3DMagnet%2C%22magnet%22%2CIF(E8%2F(C8*D8)%3DForce%2C%22force%22)))%2C%22%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20like%20this%3F%20I%20entered%20above%20formula%20in%20B8%20and%20copied%20down%20to%20B20.%3C%2FP%3E%3CP%3EIf%20you%20enter%26nbsp%3B%3DC13*D13*Magnet%26nbsp%3B%20in%20cell%20E13%20along%20with%20values%20in%20cell%20C13%20and%20D13%20the%20formula%20returns%20%22magnet%22%20in%20B13.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attributed%20named%20ranges%26nbsp%3BGravity%2C%26nbsp%3BMagnet%20and%26nbsp%3BForce%20for%20cells%20C5%2C%20D5%20and%20E5.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Bonjour

J'ai plusieurs fichiers excel ou j'ai plusieurs constante et plusieurs variable

j'ai associé un nom au cellule ayant des constantes du coup mes cellules F4, G4, H4 s'appelle gravité, magnet, force

ensuite mes variable sont dans les colonnes F7, F8, F9 etc mais aussi G7, G8 etc

dans la colonne E7, E8, etc en face de mes variables, j'écris la force qui s'excerce sur mes variable soit : gravité, magnet, force 

j'aimerais pouvoir faire dans la colonne H7, H8, H9 etc en face des variables et texte donnant quelle constante est appliqué le resultat de ma formule sans écrire dans la formule le no de la cellule mais plutot en la selectionnant soit F7*E7 = resultat 

mais comme E7 est un texte Excel me sort #VALEUR

dois modifié le text gravité ou puis je mettre des guillemets ou autre à la cellule E7

totu cela pour pouvoir copier coller ma formule sur toute la page 

Merci

 

cordialement

 

olivier

 

5 Replies

@oliviermouaze 

=E7&" "&F7*G7

 

Is this what you want to do?

@Quadruple_Pawn 

unfortunatly not 

 

@Quadruple_Pawn if this can helptest.jpg

best response confirmed by oliviermouaze (New Contributor)
Solution

@oliviermouaze 

=IFERROR(IF(E8/(C8*D8)=Gravity,"gravity",IF(E8/(C8*D8)=Magnet,"magnet",IF(E8/(C8*D8)=Force,"force"))),"")

 

Maybe like this? I entered above formula in B8 and copied down to B20.

If you enter =C13*D13*Magnet  in cell E13 along with values in cell C13 and D13 the formula returns "magnet" in B13. 

I attributed named ranges Gravity, Magnet and Force for cells C5, D5 and E5.

Thanks a lot