I'm using Excel XP. When I enter a formula (sum, vlookup,
sumif, etc.) in cell A1, I'd like cell A2 to show the
actual formula text. So, A1 will show the results of the
formula, while A2 shows the actual formula itself.

So, for example, if cell C1=1 and cell C2=2 and I type
into A1 "=SUM(C1:C2)" the results will be as follows:

Cell A1 will show the result: 3
Cell A2 will show the formula text: "=SUM(C1:C2)"

I'm thinking there should be a way in VBA to return the
formula text somehow. I just don't know how to do it.

Thanks for any and all help.

C.

Craig,
Here is something that I got to work just messing around with it. There
might be a better way though.

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Cells(1, 2) = "'" & Worksheets("sheet1").Range("a1").Formula
End Sub

Hope this helps

Charlie

"Craig" wrote in message
...
I'm using Excel XP. When I enter a formula (sum, vlookup,
sumif, etc.) in cell A1, I'd like cell A2 to show the
actual formula text. So, A1 will show the results of the
formula, while A2 shows the actual formula itself.

So, for example, if cell C1=1 and cell C2=2 and I type
into A1 "=SUM(C1:C2)" the results will be as follows:

Cell A1 will show the result: 3
Cell A2 will show the formula text: "=SUM(C1:C2)"

I'm thinking there should be a way in VBA to return the
formula text somehow. I just don't know how to do it.

Thanks for any and all help.

C.

Public Function FormulaText(rng As Range) As String
FormulaText = rng.Formula
End Function

Then in A2: =FormulaText(A1)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Craig" wrote in message
...
I'm using Excel XP. When I enter a formula (sum, vlookup,
sumif, etc.) in cell A1, I'd like cell A2 to show the
actual formula text. So, A1 will show the results of the
formula, while A2 shows the actual formula itself.

So, for example, if cell C1=1 and cell C2=2 and I type
into A1 "=SUM(C1:C2)" the results will be as follows:

Cell A1 will show the result: 3
Cell A2 will show the formula text: "=SUM(C1:C2)"

I'm thinking there should be a way in VBA to return the
formula text somehow. I just don't know how to do it.

Thanks for any and all help.

C.

In the worksheet's code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target(1, 1), _
Range("A1")) Is Nothing Then
Target.Offset(, 1).Value = _
"'" & Target.Formula
End If
End Sub


"Craig" wrote in message
...
I'm using Excel XP. When I enter a formula (sum, vlookup,
sumif, etc.) in cell A1, I'd like cell A2 to show the
actual formula text. So, A1 will show the results of the
formula, while A2 shows the actual formula itself.

So, for example, if cell C1=1 and cell C2=2 and I type
into A1 "=SUM(C1:C2)" the results will be as follows:

Cell A1 will show the result: 3
Cell A2 will show the formula text: "=SUM(C1:C2)"

I'm thinking there should be a way in VBA to return the
formula text somehow. I just don't know how to do it.

Thanks for any and all help.

C.

slight change

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target(1, 1), _
Range("A1")) Is Nothing Then
Target(1, 1).Offset(, 1).Value = _
"'" & Target(1, 1).Formula
End If
End Sub

"Tim Zych" wrote in message
...
In the worksheet's code area:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target(1, 1), _
Range("A1")) Is Nothing Then
Target.Offset(, 1).Value = _
"'" & Target.Formula
End If
End Sub


"Craig" wrote in message
...
I'm using Excel XP. When I enter a formula (sum, vlookup,
sumif, etc.) in cell A1, I'd like cell A2 to show the
actual formula text. So, A1 will show the results of the
formula, while A2 shows the actual formula itself.

So, for example, if cell C1=1 and cell C2=2 and I type
into A1 "=SUM(C1:C2)" the results will be as follows:

Cell A1 will show the result: 3
Cell A2 will show the formula text: "=SUM(C1:C2)"

I'm thinking there should be a way in VBA to return the
formula text somehow. I just don't know how to do it.

Thanks for any and all help.

C.

I have a page on the topic, with some variations
GetFormula (similar to Ron's), GetFormulaD, GetFormat, etc.

Show FORMULA or FORMAT of another cell
http://www.mvps.org/dmcritchie/excel/formula.htm
--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Rob van Gelder" wrote ...
Public Function FormulaText(rng As Range) As String
FormulaText = rng.Formula
End Function

Then in A2: =FormulaText(A1)

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Craig" wrote in message
...
I'm using Excel XP. When I enter a formula (sum, vlookup,
sumif, etc.) in cell A1, I'd like cell A2 to show the
actual formula text. So, A1 will show the results of the
formula, while A2 shows the actual formula itself.

So, for example, if cell C1=1 and cell C2=2 and I type
into A1 "=SUM(C1:C2)" the results will be as follows:

Cell A1 will show the result: 3
Cell A2 will show the formula text: "=SUM(C1:C2)"

I'm thinking there should be a way in VBA to return the
formula text somehow. I just don't know how to do it.

Thanks for any and all help.

C.

Thanks for all your help!

-----Original Message-----
I'm using Excel XP. When I enter a formula (sum,
vlookup,
sumif, etc.) in cell A1, I'd like cell A2 to show the
actual formula text. So, A1 will show the results of the
formula, while A2 shows the actual formula itself.

So, for example, if cell C1=1 and cell C2=2 and I type
into A1 "=SUM(C1:C2)" the results will be as follows:

Cell A1 will show the result: 3
Cell A2 will show the formula text: "=SUM(C1:C2)"

I'm thinking there should be a way in VBA to return the
formula text somehow. I just don't know how to do it.

Thanks for any and all help.

C.
.