Concat Function keeps values as function and when dragged to the cell below doesn't change cell reference

[SRSAND1960]

Greetings,
I've spent a few hours trying to figure out why I have to highlight the concat, textjoin, Concatenate function and hit F9 to get the values to show as the string that I want. I have searched and searched. My advanced options are set correctly, (auto). I tried switching to Manual and back, that did nothing.
Problem 1: My formula is easy =CONCAT(C3,"-",D3,"-",E3,"-",F3,"-",G3). When I select it and hit F9 it displays the value. I have typed this in and used the Fx option. It doesn't make a difference on the execution.
Problem 2: Same formula when I click as a Function and drag down to the cell below the cell #s in the reference do not change e.g., in H3 I enter =CONCAT(C3,"-",D3,"-",E3,"-",F3,"-",G3) I copy it down to H4 and it is still =CONCAT(C3,"-",D3,"-",E3,"-",F3,"-",G3). I've also tried the =C3&"-"&D3&"-"&E3&"-"&F3&"-"&G3, when copied the function goes to =C3&"-"&D3&"-"&E3&"-"&F3&"-"&G4, but the other numbers don't change.

Please help anyone... lost on an island and this is my note in a bottle...
Regards,
Steve

 

[DRSteele]

Welcome to the Forum.

I see why you're sending out an SOS, an SOS to the world.

I tried all of the above and everything works as expected.

Try to start Excel in what's called Safe Mode. Do this:
Close all instances of Excel. Hold down CTRL and click on Excel to invoke it. Respond yes when asked about Safe Mode. Open a blank workbook and try your experiment with CONCAT. Close Excel.

Let us know if it works better.

 

[SRSAND1960]

Doc, that worked... but it still doesn't work in the table with my data in. I tried with and without the "-" in the blank workbook. Which started as a table and then I converted to Range. HOWEVER Doc, when I copy the columns and put them in a blank sheet in Safe Mode it works fine... all of it.
I'm unclear what is happening with the other sheet and actually spent about 5 hours Google, MS, youTube etc.
I can take what I need from the Safe Mode page and paste it in my workbook. I'm actually a wiz at MS products and this was stumping me.
What does the safe mode do and thank you kindly Doc... like a million!
Regards,
Steve

 

[SRSAND1960]

Doc, what is interesting is when I copy the whole sheet and drop it in the workbook that I had opened in safe mode the same thing happens. No Dice... When I just copy the columns I want to concat it worked. Regardless, I have the column I need for the broken workbook. Not sure what is up with that and why the Fx stays as a function either. Had me going Nuts all day and couldn't let it go.
Thank you kindly.

 

[SRSAND1960]

Actually I believe that it had something to do with the headings I had in the table as I couldn't paste the results from the safe mode. I removed row 1 with the headers and now it worked. I've done this 100 times before and nothing like this has ever happened.
Thank you again Doc.

 

[DRSteele]

Super - glad you figured it all out.

I'm only a doctor when the ladies call me that. Otherwise D and R are my initials.