I see. What threw me was I tried it on this formula...

=LOOKUP(B3,G10:G20,H10:H20)

and it returned a numerical value of 5 (for some data I set up), which is
what prompted me to reply the way I did. I just tried it on this formula...

=B2+B3

and it returned this...

=Years+5

which is not exactly correct. It did work correctly for this formula
though...

=A1+B1

So I'm not sure how universal your solution would be.

--
Rick (MVP - Excel)


"Tom Hutchins" wrote in message
...
That's what this version (document_it1) does. The .Offset(-1,0) looks at
the
cell above the cell referenced in the formula. The sample file I uploaded
also has yesterday's version (document_it2), which puts the referenced
cell's
value in the output.

Hutch

"Rick Rothstein" wrote:

I don't think that is what the OP wants... I believe he is asking for the
header text to be put in the formula in place of the cell reference, not
the
cell's value (that solution was the subject of his previous thread).

--
Rick (MVP - Excel)


"Tom Hutchins" wrote in message
...
Try this:

Sub document_it1()
Dim r As Range, v As String
Dim w As String, x
Dim y As String, z As String
Set r = ActiveCell
If InStr(r.Formula, "(") 0 Then
v = Right(r.Formula, Len(r.Formula) - InStr(r.Formula, "("))
Else
v = Right(r.Formula, Len(r.Formula) - InStr(r.Formula, "="))
End If
If InStr(v, ")") 0 Then
w = Left(v, InStr(v, ")") - 1)
Else
w = v
End If
x = Split(w, "+", -1)
y = "="
For n = LBound(x) To UBound(x)
y = y & Range(x(n)).Offset(-1, 0).Value & "+"
Next n
z = Left(y, Len(y) - 1)
r.Offset(1, 0).NumberFormat = "@"
r.Offset(1, 0).Value = z
End Sub

I have uploaded a sample file with this macro (and the version I wrote
for
your request yesterday) with some dummy data to:
http://freefilehosting.net/download/454ll

Download & open the file. Select C2 on SheetX and run the above macro.

Hope this helps,

Hutch

"Todd Virlee" wrote:

I will rephrase the question in the hopes that no one else will
belittle
me.

How does Excel know where there is a cell reference in a formula? How
does
it know to change the color when you are editing?

I want to be able to write a macro that will output the value of a
cell
above the referenced cell.

If the worksheet looks like this

Today Years New date in future
2/11/2009 5 2/14/2009

where C2's formula is A2+B2, I want to get an output of "=Today+Years"
in
C3.

"Rick Rothstein" wrote:

This newsgroup gets hundreds of messages posted to it daily. As a
result,
almost no one here would remember what you posted yesterday and you
shouldn't expect them to go looking for your past messages either.
By
the
way, I did look for your past post and can find no other posts by
you
in
this newsgroup. Perhaps you posted it in a different newsgroup...
again, you
shouldn't expect anyone to go looking (all over the net) for it. It
is
always best to state your existing conditions and what you are
trying
to
accomplish with it... and giving examples of existing and what you
want
afterwards is always helpful too.

--
Rick (MVP - Excel)


"Todd Virlee" wrote in
message
...
I am trying to translate a formula into meaningful text like I
posted
yesterday. How can I get tell where there is an A1 or C1 in the
formula?