Display cell contents in visible formula rather than cell ref in Exce?
Display cell contents in visible formula rather than cell ref in Exce?
(OP)

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
RE: Display cell contents in visible formula rather than cell ref in Exce?
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;"#") & "+" & TEXT(B2;"#") & "+" & TEXT(C3;"#") & "=" & TEXT(A1+B2+C3;"#")'
but really cumbersome and wasteful and not useful.
prex
motori@xcalcsREMOVE.com
http://www.xcalcs.com
Online tools for structural design
RE: Display cell contents in visible formula rather than cell ref in Exce?
RE: Display cell contents in visible formula rather than cell ref in Exce?
RE: Display cell contents in visible formula rather than cell ref in Exce?
RE: Display cell contents in visible formula rather than cell ref in Exce?
RE: Display cell contents in visible formula rather than cell ref in Exce?
You could easily do this with VBA:
Sub DisplayFormula()
Range("F1") = Range("A1").Value & " + " & _
Range("B1").Value & " + " & Range("C1").Value & _
" = " & Range("D1").Value
Range("F2") = Range("A2").Value & " + " & _
Range("B2").Value & " + " & Range("C2").Value & _
" = " & Range("D2").Value
Range("F3") = Range("A3").Value & " + " & _
Range("B3").Value & " + " & Range("C3").Value & _
" = " & Range("D3").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
RE: Display cell contents in visible formula rather than cell ref in Exce?
Thanks for your suggestion. I believe your method will work for my needs.
Stick1
RE: Display cell contents in visible formula rather than cell ref in Exce?
Function Formul(Cel As Range)
Set excel = GetObject(, "Excel.Application")
Set excelSheet = excel.ActiveWorkbook.ActiveSheet
FormulText = Cel.Formula
For i = 1 To Len(FormulText) + 1
FormulChar = Mid(FormulText, i, 1)
If FormulChar <> "+" And FormulChar <> "-" And FormulChar <> "*" And FormulChar <> "/" And FormulChar <> "=" And FormulChar <> "(" And FormulChar <> ")" And FormulChar <> "" Then
FormulRef1 = FormulRef1 & FormulChar
FormulRef2 = ""
Else:
If FormulRef1 <> "" Then
If IsNumeric(FormulRef1) = True Then
FormulRef1 = FormulRef1
Else
FormulRef1 = Format(excel.Range(FormulRef1), "0.00")
End If
End If
FormulRef2 = FormulChar
Formul = Formul & FormulRef1 & FormulRef2
FormulRef1 = ""
End If
Next
End Function
RE: Display cell contents in visible formula rather than cell ref in Exce?
RE: Display cell contents in visible formula rather than cell ref in Exce?