Concatenate cell showing 0

[Sharksfan]

I enter the following formula in a cell, =concatenate("Suggested ROP for "&G1). The cell show a 0 (zero). I click on "show formulas" and the formula is gone. Just a 0 in the cell. Using Excel 365. I have not had this issue with other versions of excel. I have had this issue before. I have tried multiple ways of entering including using the "insert Fx" and entering directly into the cell as ="Suggested ROP for "&G1. same results.

 

[Joe4]

Do you have any VBA code in this workbook that could be overwriting it?
Specifically, look for code in that particular sheet module in VBA.

 

[Sharksfan]

Do you have any VBA code in this workbook that could be overwriting it?
Specifically, look for code in that particular sheet module in VBA.

No VBA code in the workbook or being executed.

 

[Joe4]

Does it do this in just this one workbook?

If you open a brand new Excel workbook and try it in there, does it display this type of behavior?

 

[Joe4]

By the way, it shouldn't be affecting this, but you aren't quite using the CONCATENATE function properly.

With the CONCATENATE function, you list each piece you want to combine separated by a comma, i.e.

=CONCATENATE("Suggested ROP for ",G1)

Alternatively, you can skip the CONCATENATE function altogether, and simply use ampersands ("&") to sew the pieces together, i.e.

="Suggested ROP for " & G1

It seems you combined both methods together, creating an unnecessary redundancy.

 

[Sharksfan]

I mis-typed it in my question above. I do us the "," as a separator for the two arguments. Funny thing is I can type the formula in the cell above (L1) and it works as =concatenate("suggested ROP for ",$G$1). When I copy (drag) it down to the cell I need it in (L2), it copies it as values, the formula disappears. when I change the value of G1, nothing happens. I had the same issue with other cells in this row. There must be some setting that is preventing formulas in this row. The workbook, sheet, cells are not protected.

 

[Joe4]

Can you answer the questions I posted in post 4?

 

[Sharksfan]

I figured it out. I inherited this spreadsheet from another is is no longer around. I am trying to a formula in a row that is used for column headers of a table.