How to display cell values in formula instead of references?

[lawi227]

For example:

When I click in G5, the formula is: "='Benchmark Inputs '!F18/'Benchmark Inputs '!F10"

Instead, I would like to see: $2,398,528,746 / $4,219,000,000
The values can be displayed in H5.

My formulas have a variety of general, currency, and percentage formatting.

I'm ultimately using this as a way to confirm my calculations and show my work. I would prefer to not use VBA.

Thanks!

 

[u0107]

try =CONCATENTATE((Formula 1)," / ",(Formula 2))

 

[Rick Rothstein]

I know you said you did not want a VBA solution, but in case you change your mind, the following macro will place a formula (prefixed with an apostrophe to preserve text) where the cell references have been replaced by their values for all cells in the single column selection in the next column over from the selected cells (so that next column needs to be empty or you need to insert a column next to the column with the selected cell before running the macro). Also read the note at the code for a minor limitation.

[table="width: 500"]
[tr]
	[td]Sub CheckCellReferences()
  Dim ShapeCount As Long, Arrow As Long, Link As Long, Addr As String, Frmla As String
  Dim Cell As Range, CurrentCell As Range, OriginalSheet As String, OriginalCell As String
  Application.ScreenUpdating = False
  OriginalSheet = ActiveSheet.Name
  OriginalCell = ActiveCell.Address
  ShapeCount = ActiveSheet.Shapes.Count
  For Each Cell In Selection
    Set CurrentCell = Cell
    Frmla = Replace(CurrentCell.Formula, "$", "")
    If CurrentCell.HasFormula Then
      CurrentCell.ShowPrecedents
      Link = 1
      For Arrow = 1 To ActiveSheet.Shapes.Count - ShapeCount
        On Error Resume Next
        Do
          CurrentCell.Parent.Activate
          CurrentCell.Activate
          Addr = CurrentCell.NavigateArrow(True, Arrow, Link).Address
          If Err.Number Then
            Link = 1
            Exit Do
          End If
          Frmla = Replace(Frmla, ActiveCell.Address(0, 0), ActiveCell.Text)
          Frmla = Replace(Frmla, ActiveCell.Parent.Name & "!", "")
          Frmla = "'" & Replace(Frmla, "'" & ActiveCell.Parent.Name & "'!", "")
          Link = Link + 1
Continue:
        Loop
        Cell.Offset(, 1) = Frmla
      Next
      CurrentCell.ShowPrecedents Remove:=True
    End If
    Worksheets(OriginalSheet).Activate
    Range(OriginalCell).Activate
  Next
  Application.ScreenUpdating = False
End Sub[/td]
[/tr]
[/table]

NOTE: If your formula has a text value that looks like a cell reference, for example the A12 in "Serial Number A12-345", and one of the cell references in the formula is actually A12, then the A12 inside the text constant will be replace along with the actual cell reference in the formula... I do not know a way around this should it occur.