I wasn't attempting to belittle you, only to educate you on what we
volunteers need to work with in trying to answer your, as well as all the
other poster's, questions. You question in this thread was a little thin on
the detail you wanted and it looked like you were saying "go find my other
post to find out what I am looking for". By the way, I do apologize for
saying I couldn't find your other thread... I'm not sure what happened but,
when I first looked, I could find any other posts by you... I just looked
again and this time I see them.

Anyway... to your question. The macro I am posting below works, but only for
simple cell references. If you have any range references, those will remain
as is. Also, the macro will not work for references on other worksheets
(seems to be a limitation of the Precedents property). I **might** be able
to develop code to work around that, but I'm thinking it will be difficult
(unless all your worksheet references always had apostrophes around their
names... I'm thinking that might let me hone in on the worksheet's name
easier).

To use the following macro, select the cell or cells you want to perform
this operation on and then run the macro. One thing you will need to do
manually is set the location for the output as a row/column offset from the
selected cell or cells. To do this, just change the two Const statements to
set the row offset (0 means same row) and the column offset (0 means same
column). As set in my code, I post the formulas in the row underneath the
selected cell or cells (as per your stated desire to have C2's modified
formula posted in C3). Note that to tell which is the Row 1 header text,
that text is listed with angle brackets around it. For example, if the
header text is Year, it will be shown in the modified formula as <Year so
that you can tell it from the rest of the text (function names, etc.) that
may be in the formula as well...

Sub ReferenceHeaderText()
Dim R As Range, V As Range
Dim CellFormula As String

Const RowOffset As Long = 0
Const ColumnOffset As Long = 1

For Each R In Selection
CellFormula = Replace(R.Formula, "$", "")
For Each V In R.Precedents
If InStr(CellFormula, V.Address(0, 0) & ":") = 0 And _
InStr(CellFormula, ":" & V.Address(0, 0)) = 0 Then
CellFormula = Replace(CellFormula, V.Address(0, 0), "<" & _
Cells(1, V.Column).Value & "")
End If
Next
With R.Offset(RowOffset, ColumnOffset)
.NumberFormat = "@"
.Value = CellFormula
End With
Next
End Sub

--
Rick (MVP - Excel)


"Todd Virlee" wrote in message
...
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?