Display cell contents in visible formula rather than cell ref in Exce?

[stick1]

%-)
Hello,
I would like to create a spreadsheet that displays formulas showing the sum of the various cell contents instead of showing the cell references. This is a way to evaluate several scenarios by looking at the values involved. For instance, I have the cells as follows:
A B C
1 2500 5000 7500
2 50 75 100
3 10 20 30

Can I display sums in other cells using a formula so they look like:

2500 + 100 + 20 = 2620 instead of A1+C2+B3?
5000 + 75 + 10 = 5085 instead of B1+B2+A3?

Thanks

 

[prex]

Not really clear what you are looking for.
If you write in a cell '=2500+100+20' of course 2620 will be displayed, but the formula remains there.
Otherwise, if you want to really display the formula + result you should use something like
'= TEXT(A1;"#&quot & "+" & TEXT(B2;"#&quot & "+" & TEXT(C3;"#&quot & "=" & TEXT(A1+B2+C3;"#&quot'
but really cumbersome and wasteful and not useful. prex
motori@xcalcsREMOVE.com

http://www.xcalcs.com

Online tools for structural design

 

[melone]

Tools->Options->View->Window Options->Formulas

 

[melone]

Sorry, just re-read your post and my solution doesn't work!

 

[Gearhead]

there is an Add-in available at spreadsheet world which dispalys formulas and there references in 2d format and then places them back in the spreadsheet for reference.

 

[Gearhead]

I am sorry I forgot to tell you the name of the software is MathLook For Excel

 

[jproj]

stick1:

You could easily do this with VBA:

Sub DisplayFormula()
Range("F1&quot = Range("A1&quot.Value & " + " & _
Range("B1&quot.Value & " + " & Range("C1&quot.Value & _
" = " & Range("D1&quot.Value

Range("F2&quot = Range("A2&quot.Value & " + " & _
Range("B2&quot.Value & " + " & Range("C2&quot.Value & _
" = " & Range("D2&quot.Value

Range("F3&quot = Range("A3&quot.Value & " + " & _
Range("B3&quot.Value & " + " & Range("C3&quot.Value & _
" = " & Range("D3&quot.Value
End Sub

This assumes you have the sum of columns A-C in column D and the "formula" is listed in column F.

You could even write a custom function to do the same thing if you wanted. If you don't want to deal with VBA, I think prex's suggestion does the same thing.

Good Luck!

jproj

 

[stick1]

Prex,
Thanks for your suggestion. I believe your method will work for my needs.

Stick1

 

Here is my way:


Function Formul(Cel As Range)
Set excel = GetObject(, "Excel.Application&quot
Set excelSheet = excel.ActiveWorkbook.ActiveSheet
FormulText = Cel.Formula
For i = 1 To Len(FormulText) + 1
FormulChar = Mid(FormulText, i, 1)
If FormulChar <> &quot;+&quot; And FormulChar <> &quot;-&quot; And FormulChar <> &quot;*&quot; And FormulChar <> &quot;/&quot; And FormulChar <> &quot;=&quot; And FormulChar <> &quot;(&quot; And FormulChar <> &quot&quot; And FormulChar <> &quot;&quot; Then
FormulRef1 = FormulRef1 & FormulChar
FormulRef2 = &quot;&quot;
Else:
If FormulRef1 <> &quot;&quot; Then
If IsNumeric(FormulRef1) = True Then
FormulRef1 = FormulRef1
Else
FormulRef1 = Format(excel.Range(FormulRef1), &quot;0.00&quot
End If
End If
FormulRef2 = FormulChar
Formul = Formul & FormulRef1 & FormulRef2
FormulRef1 = &quot;&quot;
End If
Next
End Function

 

[Gearhead]

Loooks Like a lot of work to me. The add-in is only $49.00

 

[brengine]

Would &quot;Naming&quot; the cell work?