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

agwalsh
agwalsh used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
#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.
did you have a named range when opened in O365?

Author

Commented:
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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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?

Author

Commented:
I opened up the file in Excel 2010 and it now says {=_xlfn.Single( everywhere I had sumifs...
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
If the SUMIFS function is using any range names which are constructed using the formulas available only in higher version not in 2010, you will get that error.

If not, try manually editing the formula, delete the existing SUMIFS and retype SUMIFS part in the formula and see if that resolves the issue.

Author

Commented:
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

Author

Commented:
Thank you to everyone who helped. I really appreciated it. Had to recreate the formulas over 10 sheets...
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!
Roy CoxGroup Finance Manager

Commented:
You could have tried using Find & Replace to change the formulas
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
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).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial