SOLVED

Afficher le n° de ligne de la référence d'une cellule

%3CLINGO-SUB%20id%3D%22lingo-sub-2374836%22%20slang%3D%22fr-FR%22%3EView%20a%20cell's%20reference%20line%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2374836%22%20slang%3D%22fr-FR%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EMy%20problem%3A%3C%2FP%3E%3CP%3EA1%20-%2010%3C%2FP%3E%3CP%3EA2%20-%2020%3C%2FP%3E%3CP%3EA3%20-A1-A2%3C%2FP%3E%3CP%3EA4%20-A2%20--%26gt%3B%20display%2020%3C%2FP%3E%3CP%3EI%20would%20like%20to%20display%20in%20B4%20the%20row%20number%20of%20the%20cell%20reference%20in%20A4%3C%2FP%3E%3CP%3EI%20would%20like%20something%20like%20B4%20-%3CSTRONG%3ELINE(XXX%3C%2FSTRONG%3E(A4))-2%3C%2FP%3E%3CP%3Eany%20idea%20for%20%3CSTRONG%3EXXX%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EThanks%20in%20advance%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2374836%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375016%22%20slang%3D%22en-US%22%3ERe%3A%20Afficher%20le%20n%C2%B0%20de%20ligne%20de%20la%20r%C3%A9f%C3%A9rence%20d'une%20cellule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375016%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059731%22%20target%3D%22_blank%22%3E%40Sylvain19%3C%2FA%3E%26nbsp%3BThe%20formula%20%3DROW(A2)%2C%20or%20%3CSTRONG%3E%3DLIGNE(A2)%3C%2FSTRONG%3E%2C%20in%20French%20will%20return%20the%20number%202.%20If%20this%20is%20not%20what%20you%20are%20asking%2C%20please%20upload%20an%20example%20workbook%2C%20rather%20that%20writing%20what%20each%20cell%20contains%20in%20words.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375122%22%20slang%3D%22en-US%22%3ERe%3A%20Afficher%20le%20n%C2%B0%20de%20ligne%20de%20la%20r%C3%A9f%C3%A9rence%20d'une%20cellule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375122%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059731%22%20target%3D%22_blank%22%3E%40Sylvain19%3C%2FA%3E%26nbsp%3BStill%20don't%20get%20it.%3C%2FP%3E%3CP%3ECell%20A4%20contains%20a%20reference%20to%20A2.%20Just%20enter%26nbsp%3B%26nbsp%3B%3CSTRONG%3E%3DROW(A2)%3C%2FSTRONG%3E%20in%20B4%20it%20will%20result%20in%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20perhaps%20you%20want%20to%20find%20the%20row%20number%20of%20the%20cell%20that%20is%20equal%20to%20the%20value%20in%20A4%3F%20Then%20you%20could%20use%20MATCH.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375894%22%20slang%3D%22en-US%22%3ERe%3A%20Afficher%20le%20n%C2%B0%20de%20ligne%20de%20la%20r%C3%A9f%C3%A9rence%20d'une%20cellule%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375894%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059731%22%20target%3D%22_blank%22%3E%40Sylvain19%3C%2FA%3E%26nbsp%3BNot%20the%20most%20elegant%2C%20but%20this%20should%20work%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DVALUE(SUBSTITUTE(FORMULATEXT(B7)%2C%22%3DB%22%2C%22%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ESee%20attached%20in%20C8.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2375958%22%20slang%3D%22fr-FR%22%3ERe%3A%20Show%20a%20cell's%20reference%20line%20number%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2375958%22%20slang%3D%22fr-FR%22%3EThanks%20a%20lot%20for%20your%20big%20help.%3CBR%20%2F%3EFunction%20Formulatext%20is%20a%20very%20good%20idea.%3CBR%20%2F%3EIt%20works%20with%20all%20rows%2C%20I%20try%20this%3A%20%3CBR%20%2F%3E%20'STXT(FORMULETEXTE(B7)%3B3%3B99)%20%3CBR%20%2F%3E%20Have%20a%20nice%20weekend%2C%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

My problem:

A1 = 10

A2 =  20

A3 =A1+A2

A4 =A2 --> display 20

I would like to display in B4 the row number of the cell reference in A4

I would like something like B4 = LINE(XXX(A4))=2

any idea for XXX?

Thanks in advance,

 

 

Merci

 

6 Replies

@Sylvain19 The formula =ROW(A2), or =LIGNE(A2), in French will return the number 2. If this is not what you are asking, please upload an example workbook, rather that writing what each cell contains in words.

Hi @Riny_van_Eekelen 

Thanks a lot for your answer.

Please find attached an example.

Sylvain

@Sylvain19 Still don't get it.

Cell A4 contains a reference to A2. Just enter  =ROW(A2) in B4 it will result in 2.

 

Or perhaps you want to find the row number of the cell that is equal to the value in A4? Then you could use MATCH. See attached.

 

@Riny_van_Eekelen 

Sorry, that's not what I search.

New example.

best response confirmed by allyreckerman (Microsoft)
Solution

@Sylvain19 Not the most elegant, but this should work:

=VALUE(SUBSTITUTE(FORMULATEXT(B7),"=B",""))

See attached in C8.

Thanks a lot for your big help.
Function Formulatext (formuletexte in French) is a very good idea.
It works with all rows, I try this :
=STXT(FORMULETEXTE(B7);3;99)
Have a nice week-end,