Hello,
I am running a Jet Report with a Formula called UNIQUE, I think it is only available in the o365 version
=UNIQUE(D5:D6)
When I run the report everything works as expected but when I go back to Design mode the formula changes to =@UNIQUE(D5:D6) after that if I run the report again the formula no longer works
Any ideas on how to fix this?
I understand the problem well. I think Jet hasn't been updated since the Spilled Ranges were introduced into 365.
When using these Spill functions and Jet Reports, the only way I've gotten them to work successfully is to convert the sheet to a Macro file and add the formula using VBA.
Microsoft created the .Formula2 VBA answer which allows the ranges to spill. So in VBA it would be something like: Sheets("Sheet1").Range("E4").Formula2 = "UNIQUE(D5:D6)"
If you (or anybody else) comes up with a better solution (I'm thinking about trying to work out if LAMDA() can be used), I would love to find out.
Until then, best of luck.
I would be curious as to why you are using Excel to create the unique list when Jet does that. NL("rows","TABLENAME","FIELDNAME", fitlers...) will generate a unique list of values.
As for the @ symbol, this is Excel putting it there and unfortunately Jet cannot do anything about it. In theory, you could try to wrap the UNIQUE in an NP("Formula") function to see if that prevents the issue. It should, in theory.
https://www.quora.com/What-does-the-symbol-do-in-Excel-It-keeps-showing-up-as-why-is-this-happening
"To summarise, it forces excel to return a single value where multiple values could potentially be returned. Apparently Excel has always been doing this in the background and they are just making its occurrence more obvious due to the addition of dynamic arrays that are replacing CTRL+SHIFT+ENTER (CSE) array type formulas as they are now considered legacy."
