Forum Discussion
Solved
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...
- It 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
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
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?
- It 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#post5790107If 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.
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.
- interesting. 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.
