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
Last Comment
byundt
did you have a named range when opened in O365?
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.
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?
ASKER
I opened up the file in Excel 2010 and it now says {=_xlfn.Single( everywhere I had sumifs...
ASKER CERTIFIED SOLUTION
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.
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
ASKER
Thank you to everyone who helped. I really appreciated it. Had to recreate the formulas over 10 sheets...
You're welcome!
You could have tried using Find & Replace to change the formulas
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).
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).
What function you are using?
Please make sure that you haven't misspelled the function name.