Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
stick1

stick1

Mechanical
Sep 14, 2000
38
%-)
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
 
Replies continue below

Recommended for you

Sort by date Sort by votes
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;"#") & "+" & TEXT(B2;"#") & "+" & TEXT(C3;"#") & "=" & TEXT(A1+B2+C3;"#")'
but really cumbersome and wasteful and not useful. prex
motori@xcalcsREMOVE.com
Online tools for structural design
 
Upvote 0 Downvote
Tools->Options->View->Window Options->Formulas
 
Upvote 0 Downvote
Sorry, just re-read your post and my solution doesn't work!
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
I am sorry I forgot to tell you the name of the software is MathLook For Excel
 
Upvote 0 Downvote
stick1:

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
 
Upvote 0 Downvote
Prex,
Thanks for your suggestion. I believe your method will work for my needs.

Stick1
 
Upvote 0 Downvote
Here is my way:


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 <> &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
 
Upvote 0 Downvote
Loooks Like a lot of work to me. The add-in is only $49.00
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dik
  • Locked
  • Question
Convert Cell Types 1
Replies
10
Views
864
dik
dik
Kenja824
  • Locked
  • Question
excel Formula Find help
Replies
5
Views
1K
Kenja824
Kenja824
Kenja824
  • Locked
  • Question
New Formula Help 1
Replies
3
Views
765
SkipVought
SkipVought
Kenja824
  • Locked
  • Question
Formula help please
Replies
11
Views
947
SkipVought
SkipVought
Kenja824_home
  • Locked
  • Question
Excel - Looking for a formula please 2
Replies
14
Views
908
BridgeSmith
BridgeSmith

Part and Inventory Search

Sponsor

Back
Top Bottom