Avatar of agwalsh
agwalsh
 asked on

Excel file working fine in Excel 2016 opens with #Name error in 2010

I have an Excel file that works fine with my Office 365 but when opened up on Excel 2010 appears with a Name# error. I am not using any brand new functions like Xlookup or unique. Any ideas? Thanks
Microsoft OfficeMicrosoft 365Microsoft Excel

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
Subodh Tiwari (Neeraj)

#Name error means Excel doesn't recognize the function you are using.
What function you are using?
Please make sure that you haven't misspelled the function name.
E C

did you have a named range when opened in O365?
agwalsh

ASKER
The function I was using was Sumifs and it's working perfectly on my computer so I am not getting a Name#error.  I just checked with them and they have SUMIFS on their computer.  @E C - they have a number of named ranges but I've been using Sumifs on this for the last year and it was only when I upgraded a formula to add in another sumifs that they started getting this message.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
E C

your 2010 Excel - is it 32 but or 64 bit? If it’s a large spreadsheet maybe the 32 bit 2010 can’t handle the number of rows or columns?
agwalsh

ASKER
I opened up the file in Excel 2010 and it now says {=_xlfn.Single( everywhere I had sumifs...
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
agwalsh

ASKER
Went back and recreated all the formulas. I found that all of a sudden @ was appearing in front of functions. So I had to delete all those and re-do the formulas. That worked.  Thank you as always
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
agwalsh

ASKER
Thank you to everyone who helped. I really appreciated it. Had to recreate the formulas over 10 sheets...
Subodh Tiwari (Neeraj)

You're welcome!
Roy Cox

You could have tried using Find & Replace to change the formulas
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
byundt

I realize that the question has already been "answered" but the real problem is that you are running Excel 2016/Office 365 and are also an Office Insider. Some, but not all, of the Office Insiders are getting the new dynamic array feature. That's where the @ and xlfn Single are coming from.

With dynamic arrays, you no longer need to Control Shift Enter your array formulas. Excel with dynamic arrays figures out whether a function parameter is being overloaded with an array. For backwards compatibility, Excel adds the @ and xlfn.Single to indicate that a function which could return an array is actually returning only a single value.

If you delete one or more of these @ or xlfn.Single, you may get a #NAME? error when you open the workbook in an older version of Excel.

Throwing a #NAME? error is not particularly helpful to the user. I suggest sending a frown over the issue. You do this using the "smiley face" icon at the far tight of every ribbon (or by using the Help...Feedback menu item).