Formulas are not calculating if cell formatted as text

[snipescc]

I did an update last month. I'm now having an issue where sometimes a column of formulas doesn't calculate if the column is formatted as text. It took a little while to figure out that the formatting of the column was the issue. Instead, it will just show me the text of the formula, which isn't really helpful. Is there a way to change it so that it always calculates formulas, even if it is formatted as text?

I've checked and Calculation Options for workbooks is set to automatic, and 'Show Formulas' isn't selected in the Formulas tab.

 

[Joe4]

If you format a cell as Text and then make an entry into it, it will treat EVERYTHING you enter as literal text.
So it will show the text of the formula you enter, and not the result.

Do NOT format it as Text. Format it as General and enter your formulas.

 

[snipescc]

If you format a cell as Text and then make an entry into it, it will treat EVERYTHING you enter as literal text.
So it will show the text of the formula you enter, and not the result.

Do NOT format it as Text. Format it as General and enter your formulas.

Considering this has happened to me twice in the past month and never before in the last 15 years of dealing with several different workbooks a day (generally made by someone else) some change has to have happened.

It's massively annoying and it would be nice if I could go back to the way it was previously.

 

[Joe4]

Nothing changed with Excel - it has been this way for as long as I can remember (and I have been using Excel for 25 years).
The change is probably with how the "other" people/processes are creating the workbook.
I often see Excel workbook that are created by being exported out of some other software or downloaded from the internet have this issue.

 

[ItalianPlatinum]

Considering this has happened to me twice in the past month and never before in the last 15 years of dealing with several different workbooks a day (generally made by someone else) some change has to have happened.

It's massively annoying and it would be nice if I could go back to the way it was previously.

As someone who has a lot of users that use my excel workbooks and this situation has happened to me on a few ocassions. I had to limit that by setting the formulas myself (with VBA) then protecting the sheet so a user couldn't modify. If by chance they somehow applied the effort to unprotect and mess with it well I set myself in VBA right before to avoid the condition. Just an idea (below).

'Fomulas for Review
With wsComp
    lr = .Cells(rows.count, "A").End(xlUp).row
    .Unprotect   
    .Application.Calculation = xlCalculationAutomatic
    .Range("V13:V" & lr).NumberFormat = "General"
    .Range("V13:V" & lr).Formula = "=VLOOKUP(CONCATENATE(A13,C13,$V$9,"" "",$V$10),'Table'!$A$2:$F$10000,6,FALSE)"
    .Range("V13:V" & lr).NumberFormat = "@"
    .Range("D13:D" & lr).Value = .Range("D13:D" & lr).Value
    .Range("F13:V" & lr).Value = .Range("F13:V" & lr).Value
    .Columns("G:H").EntireColumn.AutoFit
    .Columns("O:V").EntireColumn.AutoFit
    .Protect DrawingObjects:=True, Contents:=True, AllowFiltering:=True
End With

 

[Joe4]

As someone who has a lot of users that use my excel workbooks and this situation has happened to me on a few ocassions. I had to limit that by setting the formulas myself (with VBA) then protecting the sheet so a user couldn't modify. If by chance they somehow applied the effort to unprotect and mess with it well I set myself in VBA right before to avoid the condition. Just an idea (below).

'Fomulas for Review
With wsComp
    lr = .Cells(rows.count, "A").End(xlUp).row
    .Unprotect  
    .Application.Calculation = xlCalculationAutomatic
    .Range("V13:V" & lr).NumberFormat = "General"
    .Range("V13:V" & lr).Formula = "=VLOOKUP(CONCATENATE(A13,C13,$V$9,"" "",$V$10),'Table'!$A$2:$F$10000,6,FALSE)"
    .Range("V13:V" & lr).NumberFormat = "@"
    .Range("D13:D" & lr).Value = .Range("D13:D" & lr).Value
    .Range("F13:V" & lr).Value = .Range("F13:V" & lr).Value
    .Columns("G:H").EntireColumn.AutoFit
    .Columns("O:V").EntireColumn.AutoFit
    .Protect DrawingObjects:=True, Contents:=True, AllowFiltering:=True
End With

That sounds like the opposite situation. Your solution looks like it is for a situation where you are creating the file, and others are using it.
It sounds to me like in this situation, someone else is creating the file, and the OP is the one trying to use it.

I had a similar situation at my previous job where we got daily Excel exports from another software program that had all sorts of issues. So I created various "clean-up" scripts and stored them to my Personal Macro Workbook. Then I could run them on any workbook with a simple keyboard shortcut combination. Saved me lots of time!

 

[ItalianPlatinum]

That sounds like the opposite situation. Your solution looks like it is for a situation where you are creating the file, and others are using it.
It sounds to me like in this situation, someone else is creating the file, and the OP is the one trying to use it.

Oh I may have missed that statement, oops

 

[Peter_SSs]

I agree with Joe that nothing has changed in relation to this behaviour, but it does depend on what has happened before the formulas have been entered.
From your image it appears that the 'age' column is formatted as Text (the numbers are left-aligned).
My guess is that you, or somebody else, has inserted a new column to enter those formulas. If a new column is inserted immediately to the right of a Text column, by default**, that new column will also be Text.

Try this experiment to see some of the issues.

1. Start a new worksheet. All columns/Cells would normally be formatted as 'General'
2. Select column B by clicking its heading letter.
3. Format that column as Text
4. Enter a number in B2. It should be left-aligned because that column was formatted as Text
5. Select C2 and enter the formula =NOW()
   You may have to widen the column but then in C2 you should see a date & Time
6. Select column C by clicking its heading letter & right click -> Insert
   A new column C should be inserted
7. Select the new C2 and enter the formula =NOW()
   This time you should see the formula, not the result because that newly inserted column took its formatting from the Text column to its left.

** That default behaviour can be over-ridden. This image was taken after step 6 above

If I click that paintbrush icon you can see the default option but also two other choices that are available.