Forum Discussion
Kay_Engineer
Feb 10, 2023Copper Contributor
Identical Excel formula work on one sheet but no another
Hi I am working on a spreadsheet from my colleague and I would like to use the & function or the CONCAT() function however it is not working on the spreadsheet they left me, however, when I have crea...
- Feb 10, 2023It is fixed now, it is a setting problem, please check the solution here in case anyone needs it.
https://www.excelforum.com/excel-formulas-and-functions/1399559-identical-excel-formula-work-on-one-sheet-but-not-another.html#post5790107
Kay_Engineer
Feb 10, 2023Copper Contributor
https://1drv.ms/x/s!ApRAKxWiqaZBhJ5VdLQZ9ltrEU85Hg?e=wMTZn8
The cell C9 is only a number, 1, not any type of calculation
The cell C9 is only a number, 1, not any type of calculation
bosinander
Feb 10, 2023Iron Contributor
I cannot access the file. Is it same result also if you change from C9 to another cell, eg an empty cell that you then enter 1 into?
Normally, it should be that the result in C9 cannot be treated as a string.
The result from the four tests?
Normally, it should be that the result in C9 cannot be treated as a string.
The result from the four tests?
- Kay_EngineerFeb 10, 2023Copper ContributorIt is fixed now, it is a setting problem, please check the solution here in case anyone needs it.
https://www.excelforum.com/excel-formulas-and-functions/1399559-identical-excel-formula-work-on-one-sheet-but-not-another.html#post5790107- jjjjjNov 12, 2024Copper Contributor
If that selection is already off and we are still seeing the issue of one workbook's formula works and the other doesn't what other options do we have? formatting, text, etc is all identical yet throws a DIV/0! error and excels solution to this is to use an IF formula but it is an IF formula that is causing this error.
- Patrick2788Feb 10, 2023Silver Contributor
- bosinanderFeb 10, 2023Iron Contributor
So, Excel still concatenates if it is possible to convert the cells result/value to a text/string, being not possible with an error.
Thanks - I did not really expect such setting to be of weight today.
- mtarlerFeb 10, 2023Silver Contributorinteresting. I couldn't find that setting on the worksheet, you must get to it using desktop app?
FYI the article says:
You have a Lotus 1-2-3 compatibility option checked for that sheet that is interfering with formula evaluation. Go to File, Options, Advanced, and uncheck the "Transition formula evaluation" option.