User Defined Function

[Bob]

I have a large spreadsheet which is not a table or database (has blank
lines). As an example of my problem, in row 500, column F I may have a
formula such as =F200. In column C of row 500 I would like to pull the data
from columns A (a schedule number) and B (a row number) of row 200. I need
the formula in C500 to recognize the formula reference from F500 and pull
the associated info from column A.

So, if the formula in F500 is =F200, the formula if manually input into
C500 would be:

="Reference "&A200&" "&B200

Can anyone assist in creating a User Defined Function to accomplish this?
Thanks in advance.

 

[Tom Ogilvy]

set rng = Range("F500").DirectDependents
rw = rng(1).Row
Range("C500").Formula = _
"=""Reference ""&A" & rw & "&"" ""&B" & rw

 

[Bernie Deitrick]

Bob,

Function OtherCell(inCell As Range, myCol As Integer) As Variant
Dim myRow As Long
myRow = Range(Replace(inCell.Formula, "=", "")).Row
OTherCell = Cells(myRow, myCol).Value
End Function

Used like this, in cell C500

OtherCell(F500,1)

to pull in the data from A200.

HTH,
Bernie
MS Excel MVP

 

[Bob]

That did the trick! Thanks very much for your assistance.