Formula won't calculate - decimal settings?

[Rijnsent]

I bumped into something weird: some formulas won't auto-calculate and it seems to me that it has to do with the decimal symbol. My model both runs on systems that have a . as the decimal symbol and the , as a decimal symbol (let's call them dot and comma). So I created some functions to format text, but have to do add some catches to make that work: =TEXT(1.234,"0.00") will show "001" with a comma-system and 1.23 with a dot-system. So I created a cell to determine the decimal symbol: =MID(""&1/10,2,1). That works like a charm and gives me a dot or a comma which I can than use in TEXT formulas.

However: sometimes when my co-worker with a dot setting has worked on the file and I (with a comma setting) pick it up, that cell will show a dot when I open the file (being: his decimal symbol) as an outcome of that formula. That cell/formula simply does not recalculate... I tried: recalculate workbook, recalculate sheet, even through VBA Range("D21").recalculate, but that doesn't do anything. If I copy the cell and paste the formula in another cell, it shows the correct outcome in that new cell, but the old one still shows the dot as it hasn't recalculated. What does work: select the cell contents and press enter or in VBA Range("D21").FormulaR1C1 =Range("D21").FormulaR1C1
Similarly, there are some cells with a simple TEXT formula that also won't recalculate, I have one cell like the example (=TEXT(1.234,"0.00") ) and it will show me 1.23 when I open the file and only change to 001 once I click the cell (or do the FormulaR1C1 VBA trick).

Does this sound familiar to anyone? I can force the recalculation through VBA but would like to know why this happens?

 

[offthelip]

you could try wrapping the calculation in an if statement which includes a volatile function which will force a recalculation such as:

=IF(FALSE,TODAY(),MID(""&1/10,2,1))