using the address referenced in a linked cells
 

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?

using the address referenced in a linked cells
 

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?

using the address referenced in a linked cells
 

it is likely that there will be many instances of the value the actual value
of that cell A1 returns is not of interest.


"Billy Liddel" wrote:

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?

using the address referenced in a linked cells
 

Emily

I'm not sure which row you want to find. and MATCH will not find a true
value if the list is not sorted.

Say you wanted to know how many values in the list are equal to A1, then use
this function:

=SUMPRODUCT(--(A2:A400=A1))

You can then use this User Defined Function (UDF) to find the row of the
instance. For example my list contained three Cats. To find the row of the
second cat in the list you will enter:

=findrow(A1,A4:A400,2)

To find the last row type:
=findrow(A1,A4:A400)

To make this work you have to copy the following code into a Visual Basic
Module.
Press ALT + F11, Insert Module then Paste the code into the module, close
the module, FileClose and enter the formula in the worksheet. The code is
between the Lines
' ================================================
Option Explicit

Function FindRow(ByVal Ref As Range, Data As Range, Optional Instance As
Long) As Long
Dim c As Variant
Dim row As Long
Dim Counter As Long

Select Case Instance

Case Is = 0 ' or missing, find last row
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
FindRow = row
End If
Next

Case Is 0 ' find the row of the Nth instance
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
Counter = Counter + 1
If Counter = Instance Then
FindRow = row
Exit Function
End If
End If
Next

End Select

End Function

' ============================================
Hope this give you what you want.

Peter
"Emily" wrote:

it is likely that there will be many instances of the value the actual value
of that cell A1 returns is not of interest.


"Billy Liddel" wrote:

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?

using the address referenced in a linked cells
 

Sorry, i think I have not explained myself very well.
I want to return the row number of the cell referenced in the formulae on A1.
So if the formula in A1 is =A200 200 would be returned.

The spreadsheet may not have any llists in it and the reference could be to
any cell on the spreadsheet.
I could do
cell('row",A200) or row(A200).

but it means viewing the formulae working out at cell A1 contains a link to
A200. I may as well type 200 in the cell. i have over 100 cells I need to
do this for. I think if i could return the formulae as string I might get
somewhere.
Is this any clearer?
thanks Emily

"Billy Liddel" wrote:

Emily

I'm not sure which row you want to find. and MATCH will not find a true
value if the list is not sorted.

Say you wanted to know how many values in the list are equal to A1, then use
this function:

=SUMPRODUCT(--(A2:A400=A1))

You can then use this User Defined Function (UDF) to find the row of the
instance. For example my list contained three Cats. To find the row of the
second cat in the list you will enter:

=findrow(A1,A4:A400,2)

To find the last row type:
=findrow(A1,A4:A400)

To make this work you have to copy the following code into a Visual Basic
Module.
Press ALT + F11, Insert Module then Paste the code into the module, close
the module, FileClose and enter the formula in the worksheet. The code is
between the Lines
' ================================================
Option Explicit

Function FindRow(ByVal Ref As Range, Data As Range, Optional Instance As
Long) As Long
Dim c As Variant
Dim row As Long
Dim Counter As Long

Select Case Instance

Case Is = 0 ' or missing, find last row
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
FindRow = row
End If
Next

Case Is 0 ' find the row of the Nth instance
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
Counter = Counter + 1
If Counter = Instance Then
FindRow = row
Exit Function
End If
End If
Next

End Select

End Function

' ============================================
Hope this give you what you want.

Peter
"Emily" wrote:

it is likely that there will be many instances of the value the actual value
of that cell A1 returns is not of interest.


"Billy Liddel" wrote:

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?

using the address referenced in a linked cells
 

Emily wrote:
Sorry, i think I have not explained myself very well.
I want to return the row number of the cell referenced in the formulae on A1.
So if the formula in A1 is =A200 200 would be returned.

The spreadsheet may not have any llists in it and the reference could be to
any cell on the spreadsheet.
I could do
cell('row",A200) or row(A200).

but it means viewing the formulae working out at cell A1 contains a link to
A200. I may as well type 200 in the cell. i have over 100 cells I need to
do this for. I think if i could return the formulae as string I might get
somewhere.
Is this any clearer?
thanks Emily



Try this:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=62

using the address referenced in a linked cells
 

You can easily inspect a formula by pressing (Ctrl + `) the ` is on the Esc
Key. This toggles the view so that formulas are displayed/not diplayed. If
you don't mind the UDF method the following will work on a simple formula:

Function GetRefRow(ByVal Ref As Range) As Variant
Dim str As String, Temp As String, row As Variant
Dim i As Long

If Ref.HasFormula Then
str = Ref.Formula
For i = Len(str) To 1 Step -1
row = Mid(str, i, 1)
If IsNumeric(row) Then
Temp = row & Temp
End If
GetRefRow = Temp
Next i
Else: Temp = "No formula"
End If
End Function

Carry out the instructions as mentioned before.

Tick Yes if this helps.

Peter

"Emily" wrote:

Sorry, i think I have not explained myself very well.
I want to return the row number of the cell referenced in the formulae on A1.
So if the formula in A1 is =A200 200 would be returned.

The spreadsheet may not have any llists in it and the reference could be to
any cell on the spreadsheet.
I could do
cell('row",A200) or row(A200).

but it means viewing the formulae working out at cell A1 contains a link to
A200. I may as well type 200 in the cell. i have over 100 cells I need to
do this for. I think if i could return the formulae as string I might get
somewhere.
Is this any clearer?
thanks Emily

"Billy Liddel" wrote:

Emily

I'm not sure which row you want to find. and MATCH will not find a true
value if the list is not sorted.

Say you wanted to know how many values in the list are equal to A1, then use
this function:

=SUMPRODUCT(--(A2:A400=A1))

You can then use this User Defined Function (UDF) to find the row of the
instance. For example my list contained three Cats. To find the row of the
second cat in the list you will enter:

=findrow(A1,A4:A400,2)

To find the last row type:
=findrow(A1,A4:A400)

To make this work you have to copy the following code into a Visual Basic
Module.
Press ALT + F11, Insert Module then Paste the code into the module, close
the module, FileClose and enter the formula in the worksheet. The code is
between the Lines
' ================================================
Option Explicit

Function FindRow(ByVal Ref As Range, Data As Range, Optional Instance As
Long) As Long
Dim c As Variant
Dim row As Long
Dim Counter As Long

Select Case Instance

Case Is = 0 ' or missing, find last row
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
FindRow = row
End If
Next

Case Is 0 ' find the row of the Nth instance
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
Counter = Counter + 1
If Counter = Instance Then
FindRow = row
Exit Function
End If
End If
Next

End Select

End Function

' ============================================
Hope this give you what you want.

Peter
"Emily" wrote:

it is likely that there will be many instances of the value the actual value
of that cell A1 returns is not of interest.


"Billy Liddel" wrote:

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?

using the address referenced in a linked cells
 

thanks that is great. I was hoping that I would not have to go that extent
and use a UDF. I have not created a UDF for many many years. I can't seem
to find instructions in the excel help to create a UDF. Can you point me in
the direction of some help.

"Billy Liddel" wrote:

You can easily inspect a formula by pressing (Ctrl + `) the ` is on the Esc
Key. This toggles the view so that formulas are displayed/not diplayed. If
you don't mind the UDF method the following will work on a simple formula:

Function GetRefRow(ByVal Ref As Range) As Variant
Dim str As String, Temp As String, row As Variant
Dim i As Long

If Ref.HasFormula Then
str = Ref.Formula
For i = Len(str) To 1 Step -1
row = Mid(str, i, 1)
If IsNumeric(row) Then
Temp = row & Temp
End If
GetRefRow = Temp
Next i
Else: Temp = "No formula"
End If
End Function

Carry out the instructions as mentioned before.

Tick Yes if this helps.

Peter

"Emily" wrote:

Sorry, i think I have not explained myself very well.
I want to return the row number of the cell referenced in the formulae on A1.
So if the formula in A1 is =A200 200 would be returned.

The spreadsheet may not have any llists in it and the reference could be to
any cell on the spreadsheet.
I could do
cell('row",A200) or row(A200).

but it means viewing the formulae working out at cell A1 contains a link to
A200. I may as well type 200 in the cell. i have over 100 cells I need to
do this for. I think if i could return the formulae as string I might get
somewhere.
Is this any clearer?
thanks Emily

"Billy Liddel" wrote:

Emily

I'm not sure which row you want to find. and MATCH will not find a true
value if the list is not sorted.

Say you wanted to know how many values in the list are equal to A1, then use
this function:

=SUMPRODUCT(--(A2:A400=A1))

You can then use this User Defined Function (UDF) to find the row of the
instance. For example my list contained three Cats. To find the row of the
second cat in the list you will enter:

=findrow(A1,A4:A400,2)

To find the last row type:
=findrow(A1,A4:A400)

To make this work you have to copy the following code into a Visual Basic
Module.
Press ALT + F11, Insert Module then Paste the code into the module, close
the module, FileClose and enter the formula in the worksheet. The code is
between the Lines
' ================================================
Option Explicit

Function FindRow(ByVal Ref As Range, Data As Range, Optional Instance As
Long) As Long
Dim c As Variant
Dim row As Long
Dim Counter As Long

Select Case Instance

Case Is = 0 ' or missing, find last row
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
FindRow = row
End If
Next

Case Is 0 ' find the row of the Nth instance
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
Counter = Counter + 1
If Counter = Instance Then
FindRow = row
Exit Function
End If
End If
Next

End Select

End Function

' ============================================
Hope this give you what you want.

Peter
"Emily" wrote:

it is likely that there will be many instances of the value the actual value
of that cell A1 returns is not of interest.


"Billy Liddel" wrote:

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?

using the address referenced in a linked cells
 

thanks that explained it all.

"Glenn" wrote:

Emily wrote:
Sorry, i think I have not explained myself very well.
I want to return the row number of the cell referenced in the formulae on A1.
So if the formula in A1 is =A200 200 would be returned.

The spreadsheet may not have any llists in it and the reference could be to
any cell on the spreadsheet.
I could do
cell('row",A200) or row(A200).

but it means viewing the formulae working out at cell A1 contains a link to
A200. I may as well type 200 in the cell. i have over 100 cells I need to
do this for. I think if i could return the formulae as string I might get
somewhere.
Is this any clearer?
thanks Emily



Try this:

http://www.vbaexpress.com/kb/getarticle.php?kb_id=62

using the address referenced in a linked cells
 

I worked out how to put in UDF functions.
It works perfectly thanks.

"Billy Liddel" wrote:

You can easily inspect a formula by pressing (Ctrl + `) the ` is on the Esc
Key. This toggles the view so that formulas are displayed/not diplayed. If
you don't mind the UDF method the following will work on a simple formula:

Function GetRefRow(ByVal Ref As Range) As Variant
Dim str As String, Temp As String, row As Variant
Dim i As Long

If Ref.HasFormula Then
str = Ref.Formula
For i = Len(str) To 1 Step -1
row = Mid(str, i, 1)
If IsNumeric(row) Then
Temp = row & Temp
End If
GetRefRow = Temp
Next i
Else: Temp = "No formula"
End If
End Function

Carry out the instructions as mentioned before.

Tick Yes if this helps.

Peter

"Emily" wrote:

Sorry, i think I have not explained myself very well.
I want to return the row number of the cell referenced in the formulae on A1.
So if the formula in A1 is =A200 200 would be returned.

The spreadsheet may not have any llists in it and the reference could be to
any cell on the spreadsheet.
I could do
cell('row",A200) or row(A200).

but it means viewing the formulae working out at cell A1 contains a link to
A200. I may as well type 200 in the cell. i have over 100 cells I need to
do this for. I think if i could return the formulae as string I might get
somewhere.
Is this any clearer?
thanks Emily

"Billy Liddel" wrote:

Emily

I'm not sure which row you want to find. and MATCH will not find a true
value if the list is not sorted.

Say you wanted to know how many values in the list are equal to A1, then use
this function:

=SUMPRODUCT(--(A2:A400=A1))

You can then use this User Defined Function (UDF) to find the row of the
instance. For example my list contained three Cats. To find the row of the
second cat in the list you will enter:

=findrow(A1,A4:A400,2)

To find the last row type:
=findrow(A1,A4:A400)

To make this work you have to copy the following code into a Visual Basic
Module.
Press ALT + F11, Insert Module then Paste the code into the module, close
the module, FileClose and enter the formula in the worksheet. The code is
between the Lines
' ================================================
Option Explicit

Function FindRow(ByVal Ref As Range, Data As Range, Optional Instance As
Long) As Long
Dim c As Variant
Dim row As Long
Dim Counter As Long

Select Case Instance

Case Is = 0 ' or missing, find last row
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
FindRow = row
End If
Next

Case Is 0 ' find the row of the Nth instance
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
Counter = Counter + 1
If Counter = Instance Then
FindRow = row
Exit Function
End If
End If
Next

End Select

End Function

' ============================================
Hope this give you what you want.

Peter
"Emily" wrote:

it is likely that there will be many instances of the value the actual value
of that cell A1 returns is not of interest.


"Billy Liddel" wrote:

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?

using the address referenced in a linked cells
 

Glad you got it sorted, thanks for the rating

Regards
Peter

"Emily" wrote:

I worked out how to put in UDF functions.
It works perfectly thanks.

"Billy Liddel" wrote:

You can easily inspect a formula by pressing (Ctrl + `) the ` is on the Esc
Key. This toggles the view so that formulas are displayed/not diplayed. If
you don't mind the UDF method the following will work on a simple formula:

Function GetRefRow(ByVal Ref As Range) As Variant
Dim str As String, Temp As String, row As Variant
Dim i As Long

If Ref.HasFormula Then
str = Ref.Formula
For i = Len(str) To 1 Step -1
row = Mid(str, i, 1)
If IsNumeric(row) Then
Temp = row & Temp
End If
GetRefRow = Temp
Next i
Else: Temp = "No formula"
End If
End Function

Carry out the instructions as mentioned before.

Tick Yes if this helps.

Peter

"Emily" wrote:

Sorry, i think I have not explained myself very well.
I want to return the row number of the cell referenced in the formulae on A1.
So if the formula in A1 is =A200 200 would be returned.

The spreadsheet may not have any llists in it and the reference could be to
any cell on the spreadsheet.
I could do
cell('row",A200) or row(A200).

but it means viewing the formulae working out at cell A1 contains a link to
A200. I may as well type 200 in the cell. i have over 100 cells I need to
do this for. I think if i could return the formulae as string I might get
somewhere.
Is this any clearer?
thanks Emily

"Billy Liddel" wrote:

Emily

I'm not sure which row you want to find. and MATCH will not find a true
value if the list is not sorted.

Say you wanted to know how many values in the list are equal to A1, then use
this function:

=SUMPRODUCT(--(A2:A400=A1))

You can then use this User Defined Function (UDF) to find the row of the
instance. For example my list contained three Cats. To find the row of the
second cat in the list you will enter:

=findrow(A1,A4:A400,2)

To find the last row type:
=findrow(A1,A4:A400)

To make this work you have to copy the following code into a Visual Basic
Module.
Press ALT + F11, Insert Module then Paste the code into the module, close
the module, FileClose and enter the formula in the worksheet. The code is
between the Lines
' ================================================
Option Explicit

Function FindRow(ByVal Ref As Range, Data As Range, Optional Instance As
Long) As Long
Dim c As Variant
Dim row As Long
Dim Counter As Long

Select Case Instance

Case Is = 0 ' or missing, find last row
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
FindRow = row
End If
Next

Case Is 0 ' find the row of the Nth instance
For Each c In Data
If LCase(c) = LCase(Ref) Then
row = c.row
Counter = Counter + 1
If Counter = Instance Then
FindRow = row
Exit Function
End If
End If
Next

End Select

End Function

' ============================================
Hope this give you what you want.

Peter
"Emily" wrote:

it is likely that there will be many instances of the value the actual value
of that cell A1 returns is not of interest.


"Billy Liddel" wrote:

=MATCH(A1,A2:A400,0)+1

Assumes that there is only one instance of CAT

HTH
Peter

"Emily" wrote:

I have a spreadsheet that has many cells linked to other cells on the same
sheet
From the links I want to know the row number of the reference.
Eg:
A1 value CAT
A200 value CAT

Cell A1 has the formula "=A200"

Using Cell A1 information only I want to know row the row number it
references , that is 200. How do i do this?