 SOLVED

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

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?

Merci

6 Replies

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

@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.

Sylvain

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

@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.

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

Sorry, that's not what I search.

New example.

best response confirmed by allyreckerman (Microsoft)
Solution

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

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

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

See attached in C8.

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

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,