General discussion

  • Creator
    Topic
  • #2318583

    Formulas visible in Excell Spreadsheet

    Locked

    by sarahsway ·

    I have a worksheet at work that I would like to display formulas in but only in a specific column. I seem to only be able to display the formulas used in the sheet on the entire worksheet. Is there any way to display these formulas for a specific column while the rest of the workbook displays the results in text?

    Thanks, Sarah

All Comments

  • Author
    Replies
    • #3363033

      Formulas visible in Excell Spreadsheet

      by dklippert ·

      In reply to Formulas visible in Excell Spreadsheet

      Here’s an answer from John Walkenbach

      http://tinyurl.com/d0ng

      “You need to create a very simple custom worksheet function using VBA. The
      steps below assume Excel 97 or later.

      1. Press Alt+F11 to activate the VB Editor
      2. Select your project in the Project window
      3. Choose Insert Module to insert a new VBA module
      4. Enter this function into the VBA module:

      Function CellFormula(c) as String
      CellFormula = c.Formula
      End Function

      Re-activate Excel by pressing Alt+F11 again. You can then use this new
      function in a formula. Here’s an example that displays the formula in cell
      A1:

      =CellFormula(A1)

      If A1 does not have a formula, the function returns the cell’s value. If
      you’d like the function to display an empty string if the cell does not have
      a formula, use this code:

      Function CellFormula(c) As String
      If c.HasFormula Then
      CellFormula = c.Formula
      Else
      CellFormula = “”
      End If
      End Function

      • #3373324

        Formulas visible in Excell Spreadsheet

        by sarahsway ·

        In reply to Formulas visible in Excell Spreadsheet

        Thank you for a quick response. Yes I was able to utilitze the function provided. Very helpful. Thanks

    • #3373323

      Formulas visible in Excell Spreadsheet

      by sarahsway ·

      In reply to Formulas visible in Excell Spreadsheet

      This question was closed by the author

Viewing 1 reply thread