Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show Formula in Specific Cell (Excel 2000)

    Okay -- this is a question from a user in my company -- not from ME! I think this whole thing is a stupid idea.

    They want to know if there is a way to show/print formulas in ONLY specific cells. I know how to "CTRL ` " or using Tools - Options - View -- in order to view all formulas in a sheet. But they are only wanting CERTAIN columns/cells to have formulas showing, and the others they want to show results as normal, and they want it to print that way.

    Can it be done? Or should I tell them they are way off-base?

    Tracy

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show Formula in Specific Cell (Excel 2000)

    Format those cells with the "TEXT" format (format - cells - number tab - Text)
    Whatever is added to the cell is shown EXACTLY as entered. (you might have to F2-enter to initialize the cells after reformatting)

    Steve

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Wichita, Kansas, USA
    Posts
    177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show Formula in Specific Cell (Excel 2000)

    Hi - thanks
    I thought of that, but unfortunately (I forgot to mention) the RESULT of that cell (the result of the formula) is then used to calculate another cell (it is a precedent for another cell). So I'm assuming changing the formatting to TEXT would mess up the dependent cell.

    Tracy

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Show Formula in Specific Cell (Excel 2000)

    Embed the entire formula in the dependent cell, such as:

    As Steve suggested, use =myformula ... formatted as text

    For the dependent cell formula use:

    =mynextformula * myformula ... formatted as a number
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show Formula in Specific Cell (Excel 2000)

    Would using split windows do what you need? you could have one set to show formulas and one normal.

    Edited later!

    Oops forget I said that! you cant print split windows. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Peter

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Show Formula in Specific Cell (Excel 2000)

    Hi Tracy,

    One way of doing this would be to add the formulas that you want displayed to the comments for the affected cells. If you then use 'show comments' for the required cells, you can display them without compromising the functionality of the underlying cells.

    Here's a macro to add the formulae to the comments for each selected cell, or even the whole worksheet:

    Sub AddFormulasToComments()
    'The following statement mainly traps errors caused by cells already containing comments.
    On Error Resume Next
    'If the whole worksheet is selected, limit action to the used range.
    If Selection.Address = Cells.Address Then
    Set CommentRange = Range(ActiveSheet.UsedRange.Address)
    Else
    Set CommentRange = Range(Selection.Address)
    End If
    'If the cell contains a formula, turn it into a comment.
    For Each oCell In CommentRange
    With oCell
    If Left(.Formula, 1) = "=" Then
    .AddComment
    .Comment.Text Text:=.Formula
    End If
    End With
    Next
    End Sub

    This macro can be called from any workbook, so you don't even have to include it in the one that is being worked on.

    After running the macro, you'll probably also want to adjust the dimensions of each comment box to fit the formula onto a single line.

    Hope this helps.

    Added:

    Alternatively, you can get the macro to automatically re-size and display the comments aligned to the right of the cell, by modifying the last IF statement:

    If Left(.Formula, 1) = "=" Then
    .AddComment
    .Comment.Text Text:=.Formula
    'the next line tests whether the comment is already displayed
    If .Comment.Visible = True Then Adjust = 0 Else Adjust = 1
    'the next line automatically displays the comment
    .Comment.Visible = True
    'the next three lines automatically resize the comment
    .Comment.Shape.Select
    With Selection
    .AutoSize = True
    'the next two lines adjust the comment's position, depending on its prior display state
    .ShapeRange.IncrementLeft -11.25 * Adjust ' you may need to change the 11.25
    .ShapeRange.IncrementTop 8.25 * Adjust ' you may need to change the 8.25
    End If
    oCell.Activate
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Show Formula in Specific Cell (Excel 2000)

    Tracy,

    this probably isn't what you want but another approach would be to put the formula for the desired cell into a comment associated with the cell. You could then print the comments out also. Of course, the cell retains its original value so that it could be used for other calculations.

    Unfortunately, I don't have the lounge post that showed how to do it - it's at work. It involved a little VBA but not much. If this would do, I'm sure that could be dug up.

    Fred

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •