Different PC's, same version excel, different results

[isskint]

This is not a "how do i fix it" question (i have already resolved the problem), more how does that happen.

I have a sheet of calculations, where the results are coerced to a text value using TEXT() (historically something to do with formatting on another sheet). Then the author has carried out more calculations using the text value cells. On 1 PC, the user sees #VALUE - which makes sense, but all other users see the correct calculated value.
I have attached a stripped down sample, the formulae in question are in Q60 + W60.

All user have windows 10 and Excel 2016 (all on the same 365 licence).

[sandy666]

If you multiply Text by Number you will get #VALUE! error.
You should define correct formula because this what is in, is incorrect.

[isskint]

hi Sandy,

Absolutely, multiply text by a number and you should get #VALUE. What did you see when you opened the sheet?

My confusion is why does only 1 user see #VALUE but everyone else sees the result of 4 x £219.91 = £879.64 (see second attachment screen print)

[sandy666]

I c a number, but if I refresh no less no more I c error,

[isskint]

That is so weird. another user gets the error after refreshing the page (or changing a value) but the other users can change multiple cells and never see the error.

Don't you just love Excel

[sandy666]

I c you've defined Name from A60 to W60 and maybe this keep temporary this value.

I do not know what this Name doing but IMHO - nothing

Excel can do everything smile3_e0.gif

[sandy666]

Convert structured table "Details" to normal range and repair your formula(s)

[isskint]

Hi Sandy,

I have completely revamped the sheet and it works fine.
As i said in my OP, converting to text was historically something to do with formatting on another sheet, but I have sorted that so all values are just values.
If i showed you more of the original sheet it would probably scare you, with some of the formulae and formatting applied to get around the authors lack of knowledge in Excel.

But i just love these little 'foibles' of Excel.

[sandy666]

you're probably right because I really, really do not like the structural tables and redundant formatting.
however, what we have to do without excel?

[xlnitwit]

Different regional settings on 1 PC, perhaps?

[isskint]

good thought , but alas not an issue

[xlnitwit]

Out of interest, if you were to remove the "£" from the formula, does it then work on that PC?

[isskint]

xlnitwit yes it does work.

you got me thinking so i also checked format applied but the same formats are set. found another horror though formatting is custom "£"0.00 and then the author used the TEXT() function to convert the cell to text!!!! Gotta love inheriting other peoples work

[xlnitwit]

There must be a language setting that is different on that PC for that user then if it's not recognising £ as the default currency symbol. Maybe he changed his editing languages in Excel, or currency format settings in Control Panel?

[sandy666]

Shorter will be if you do everything from the begining smile3_e0.gif