#NAME? error using CHOOSE function

bendy_leather2

New Member
Joined
Sep 3, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,410
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Sep 3, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

New Member
Joined
Sep 3, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,410
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,823
Members
415,859
Latest member
Vain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top