×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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?

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
http://www.xcalcs.com
Online tools for structural design

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

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

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

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

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

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.

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

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

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

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

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

(OP)
Prex,
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?

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 <> "+" 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?

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

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

Would "Naming" the cell work?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close