SOLVED

Function UNIQUE() keeps disappearing from the library!

%3CLINGO-SUB%20id%3D%22lingo-sub-2644530%22%20slang%3D%22en-US%22%3EFunction%20UNIQUE()%20keeps%20disappearing%20from%20the%20library!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2644530%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20am%20a%20long%20time%20user%20of%20Excel%20on%20Mac%20OS%2C%20using%20Office%20365%20and%20the%20latest%20Excel%20v16.52%3C%2FP%3E%3CP%3EFor%20the%20past%20couple%20of%20weeks%20I'm%20experiencing%20the%20weird%20disappearance%20of%20the%20function%20UNIQUE()%20from%20Excel%20function%20builder.%3C%2FP%3E%3CP%3E1-%20First%2C%20I%20used%20the%20function%20like%20%3DUNIQUE(Table_Vuln_Current_Week%5BApp%20Name%5D%3BFALSE)%3C%2FP%3E%3CP%3Eto%20return%20a%20list%20of%20application%20names%2C%20and%20it%20worked%20fine%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2-%20The%20next%20day%2C%20I%20open%20the%20same%20workbook%20and%20Excel%20displays%20something%20like%3C%2FP%3E%3CP%3E%3D_xlfn.UNIQUE(Table_Vuln_Current_Week%5BApp%20Name%5D%3BFALSE)%3C%2FP%3E%3CP%3Eit%20is%20greyed%20out%20and%20if%20I%20want%20to%20edit%20or%20re-enter%20the%20formula%2C%20I%20get%20a%20%23NAME%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3-%20Yesterday%20I%20got%20the%20latest%20update%20installed%20and%20the%20function%20was%20working%20again%2C%20until%20this%20morning%3C%2FP%3E%3CP%3EIf%20I%20try%20to%20create%20a%20new%20worksheet%20to%20run%20a%20test%20of%20this%20function%2C%20I%20get%20the%20%23NAME%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E4-%20I%20closed%20Excel%2C%20re-opened%20my%20work%20file%20and%20there%20it%20is%2C%20the%20formula%20is%20no%20longer%20showing%20the%20_xlfn.%20prefix%3C%2FP%3E%3CP%3EI%20use%20tons%20of%20formulas%20in%20my%20workbook%20and%20I%20only%20get%20this%20issue%20with%20the%20UNIQUE()%20and%20IFS%20functions%3C%2FP%3E%3CP%3EHas%20anyone%20encountered%20such%20weird%20behaviour%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2644530%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2655609%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20UNIQUE()%20keeps%20disappearing%20from%20the%20library!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2655609%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1127601%22%20target%3D%22_blank%22%3E%40FredL28%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20the%20issue%20resolves%20itself%20if%20I%20completely%20close%20the%20app%20Excel%20and%20re-launch%20it%3C%2FP%3E%3CP%3EBut%20every%20now%20and%20again%2C%20I%20get%20these%20errors%20and%20I%20have%20to%20close%20Excel%20and%20re-open%20the%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I am a long time user of Excel on Mac OS, using Office 365 and the latest Excel v16.52

For the past couple of weeks I'm experiencing the weird disappearance of the function UNIQUE() from Excel function builder.

1- First, I used the function like =UNIQUE(Table_Vuln_Current_Week[App Name];FALSE)

to return a list of application names, and it worked fine

 

2- The next day, I open the same workbook and Excel displays something like

=_xlfn.UNIQUE(Table_Vuln_Current_Week[App Name];FALSE)

it is greyed out and if I want to edit or re-enter the formula, I get a #NAME error

 

3- Yesterday I got the latest update installed and the function was working again, until this morning

If I try to create a new worksheet to run a test of this function, I get the #NAME error

 

4- I closed Excel, re-opened my work file and there it is, the formula is no longer showing the _xlfn. prefix

I use tons of formulas in my workbook and I only get this issue with the UNIQUE() and IFS functions

Has anyone encountered such weird behaviour?

 

Thank you

1 Reply
best response confirmed by FredL28 (New Contributor)
Solution

@FredL28 

It seems the issue resolves itself if I completely close the app Excel and re-launch it

But every now and again, I get these errors and I have to close Excel and re-open the file