Unique and Filterxml Sharepoint shared file keeps breaking

[DirkDently]

Hi, first time poster. Appreciate this website so much!

I am created a workbook shared by others on Sharepoint. It is Excel 365. I used a formula (thanks to this website) to collect employee IDs from 3 different sheets from the same column on each, to only pull the unique records. Then other formulas in other cells pull names/pay, etc.. care of lookups. The Unique/Filterxml formula keeps breaking and reverting to "#value!". It works for a while, then I have to download a copy on to my computer, I click on the formula cell, as if to edit, back out, then suddenly it works again. It is so frustrating. I can't figure it out. I can't click into the cell and have the same result while it is in the Sharepoint browser mode. It only fixes if I download the copy, and do what I indicated, then I upload it again, it works for a while then same thing happens - repeat...

It is a live file, with people adding new employee IDs, but I made lots of room for this with the other lookups. I don't get it. It works when I add new IDs on my computer when I download the file. Help?

Here is the formula:

=UNIQUE(FILTERXML("<A><B>"&TEXTJOIN("</B><B>",TRUE,FOA!A3:A87,FOD!A3:A196,FAS!A4:A98)&"</B></A>","//B"))

It works perfectly on my computer, but again, it keeps breaking after it is online and other people seem to use it. They haven't touched any of my formulas, so I'm at a loss. Thanks for your help.

Best, DD

 

[Fluff]

Hi & welcome to MrExcel, if you are using that with the browser version of Excel, that's why it doesn't work. Filterxml is not supported with the browser only desktop PCs.
Try

=unique(vstack(FOA!A3:A87,FOD!A3:A196,FAS!A4:A98))

 

[DirkDently]

Hi & welcome to MrExcel, if you are using that with the browser version of Excel, that's why it doesn't work. Filterxml is not supported with the browser only desktop PCs.
Try

=unique(vstack(FOA!A3:A87,FOD!A3:A196,FAS!A4:A98))

Oh man, ok. Thanks for this. It was making me crazy. Ok...next question then. Do you have any suggestions on how to do this function with browser version? That formula is a summary page of 3 other pages. I'm at a loss. Thanks for any suggestions you might have. Cheers

 

[DirkDently]

Oh man, ok. Thanks for this. It was making me crazy. Ok...next question then. Do you have any suggestions on how to do this function with browser version? That formula is a summary page of 3 other pages. I'm at a loss. Thanks for any suggestions you might have. Cheers

My bad, I see you posted a possible solution. Sorry, I will try that!