#NAME? error using CHOOSE function

[bendy_leather2]

Hello,

I have a workbook created in Excel 365 which uses the following formula =CHOOSE(Range_1,Range_2,Range_3)

All of the named ranges refer to single cells within the workbook.
Range_1 has another 'choose' formula that provides 1 or 2 as the outcome
Range_2 and Range_3 have formulae that sum some values on another sheet

When using this workbook in Excel 365 everything works as expected. When a colleague uses it in Excel 2016 they get #NAME? error. The named ranges are assigned to 'Workbook' and not a worksheet.

If anyone has any ideas it would be much appreciated. I've not been in a position to look at it on his laptop to see whats happening.

Thanks in advance.

 

[sandy666]

CHOOSE function - Microsoft Support

This article describes the formula syntax and usage of the CHOOSE function which uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number.

support.microsoft.com

 

[JoeMo]

Have you or your colleague looked at Name Manager to see if the named ranges are listed there and refer to the intended ranges? #NAME? indicates Excel doesn't recognize one or more names in the formula. Also, have you verified that Range_1 is returning 1 or 2.

 

[bendy_leather2]

Thanks for the prompt response!!
Range_1 is definitely returning either 1 or 2. I've not had chance to get him to look in name manager, but when i look at them in Excel 365 they appear as expected (Sheet name and cell reference, scope showing as Workbook). I'm assuming his version is having a problem with the named ranges for some reason.
The only way i can replicate the issue is by deleting or renaming a named ranges so that it doesn't match the formula. Suppose i'll have to wait until he can screen share so i can see what's happening.

 

[RoryA]

You could ask him to use the Evaluate Formula tool. Or post the formulas in each range so that we can check they are compatible with 2010.

 

[bendy_leather2]

I got my colleague to share his screen this morning and he's now showing #Value rather than #Name (i did see the original screenshot with #NAME showing). Anyway, i've since found that after sharing the workbook via sharepoint it seems to have converted some percentages to what appear to be Japanese characters (hence the #Value). I got him to change these back to percentages and its now working. FYI, the percentages were showing fine for me though... odd.
I don't know what to tell you...

 

[JoeMo]

I got my colleague to share his screen this morning and he's now showing #Value rather than #Name (i did see the original screenshot with #NAME showing). Anyway, i've since found that after sharing the workbook via sharepoint it seems to have converted some percentages to what appear to be Japanese characters (hence the #Value). I got him to change these back to percentages and its now working. FYI, the percentages were showing fine for me though... odd.
I don't know what to tell you...

Glad you got it sorted. Thanks for letting us know.