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

--
Regards,
Tom Ogilvy


"Bob" wrote in message
...
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.