Formulas Convert to Numbers Automatically

  • Stumped on this one.

    A colleague was using one of our common spreadsheets, doing some copy/pasting from another similar sheet when he called me over.

    His formulas were converting themselves to numbers.

    I saved the sheet on our network drive (the usual location) and took a look.

    If I, for example, go to cell H8 and enter "=SUM(H9:H24)" as soon as I hit enter I see the answer ($10,000). But there is no longer a formula in H8. It's a value: $10,000.

    Doesn't matter which cell I put the SUM into.

    Doesn't matter if I make a more complex argument, i.e. IF(....)

    As soon as I hit enter, the cell converts to the answer of the formula, and the formula is gone.

    Likewise, if I hit F2 and ENTER on any of the pre-existing formulas on the sheet, they convert to the answer.



  • Playing around a little....

    If I select the table's cells and paste into a new book (Ctrl-C, Ctrl-N, Ctrl-V) I can put formulas in with no issues.

    If I then do a Ctrl-C, Ctrl-V back to the original spreadsheet, I have the same issues. So it seems to be related to the sheet/workbook.

    If I copy the Worksheet into a new book, the issue persists

  • Hello,

    Have you checked if there is an event macro ?

    The best would be to attach a sample file which illustrates your problem ...;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just the bottom right corner...:)

  • Doh! =O

    I thought I had checked that. Clearly not.


    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False

    If Not Application.Intersect(Target, Range("A1:ZZ10000")) Is Nothing Then

    Target(1).Value = UCase(Target(1).Value)

    End If

    Application.EnableEvents = True

    End Sub


    Now the mystery is who put that in there, and WHY? What useless code.