Page 1 of 1

@= in active sheet formula

Posted: Mon Dec 02, 2019 4:32 pm
by MarenC
Hi,

We are seeing some strange behaviour in excel active forms. When we put in a formula and use a function it is inserting an @ symbol, so for example we see,

=@TM1RPTROW($E$10,.....)

Anyone come across this before?

Maren

Admin Edit : added search terms!
Klammeraffe , grisehale , snabel , apestaart , apestaartje , apesturtsje , apinanhanta , kissanhanta , miukumauku , kukac , majmun , arroba , zavinac , strudel, kanelbulle , snail , escargot , arobase , commercial at , chiocciola , shablul , dalphaengi , heliko, whirlpool , addeco , asperand

Re: @= in active sheet formula

Posted: Mon Dec 02, 2019 11:23 pm
by Wim Gielis
Hello,

A customer of mine reported it to me. He didn't see any adverse effects so we left it as is.

Wim

Re: @= in active sheet formula

Posted: Mon Jan 06, 2020 11:47 pm
by dymoy
Apparently Microsoft recently added that to their latest Excel upgrade patch and it's "The implicit intersection operator was introduced as part of substantial upgrade to Excel's formula language to support dynamic arrays". A link to their support page: https://support.office.com/en-us/articl ... c999be2b34.

We have noticed that the @ is affecting any uploading, particularly with AWS. Other than that, no noticeable issue. A ticket was opened with IBM by ACG.

Re: @= in active sheet formula

Posted: Tue Jan 07, 2020 8:04 pm
by Wim Gielis
Thank you, that's useful knowledge.
I wonder when Microsoft is going to roll out those new functions to those who are not part of the Insiders program. Like 99% of the Office 365 population probably.

Re: @= in active sheet formula

Posted: Fri Mar 27, 2020 1:09 pm
by Steve Rowe
In both PAfe and perspectives version 2.0.43 / TM1 Build 11.5 I can see that =@DBRW causes the type over write back to fail, instead just ending up with the value you entered in the cell?

Any ideas what release this was fixed in or does the defect still exist in the 2.0.9 series?

Cheers and Good Health too all.

Re: @= in active sheet formula

Posted: Sat Mar 28, 2020 2:34 am
by Alan Kirk
Steve Rowe wrote:
Fri Mar 27, 2020 1:09 pm
In both PAfe and perspectives version 2.0.43 / TM1 Build 11.5 I can see that =@DBRW causes the type over write back to fail, instead just ending up with the value you entered in the cell?

Any ideas what release this was fixed in or does the defect still exist in the 2.0.9 series?
I can't speak for PAfe, but in Perspectives It still exists as of the base 2.0.9 release.

I created an active form using Excel for Office 365 version 16.0.12527.20260 32 bit. None of the formulas had an @ sign.

I manually changed one of the DBRW formulas in a slice to use the @ sign. When I went to type over it I got this:
Message.png
Message.png (21.47 KiB) Viewed 455 times
If I may misquote a line from Casablanca, "I'm shocked, SHOCKED to find that gambling is going on in here and that Microsoft has lied again."

"Your formulas will behave the same"... not so much, no. As you say, entering a value just writes over the formula now.

I've seen posts in the Microsoft Communities indicating that this is an issue for a lot of UDFs (User Defined Functions) which are essentially what tm1.xla add-in formulas are.
Steve Rowe wrote:
Fri Mar 27, 2020 1:09 pm
Cheers and Good Health too all.
Indeed. Though one of the many downsides of this whole Covid business is that I have to run a lot of things through my domestic interwebz pipeline rather than through the cavernous maw that is the office interwebz feed. (I miss my desk. That's kind of disturbing.)

Anyway, I'm still waiting for the 2.0.9 IF 2.1 update that I posted about this morning to arrive down the pipe. However I'm not expecting to see any change in behaviour once I finally do have it. IBM would need to be dragged kicking and screaming to make changes to Perspectives, and with the workaound being "get rid of the @ signs if they appear" I'd be gobsmacked if they did a fix for this.

Re: @= in active sheet formula

Posted: Mon Mar 30, 2020 8:52 am
by Steve Rowe
As an FYI, this seems to clear by doing an edit replace of "=@" with "=" across all cells and sheets. I've not seen the "@" get re-introduced to date.

Re: @= in active sheet formula

Posted: Fri May 22, 2020 1:20 pm
by Steve Rowe
Thought I'd keep all the nonsense that arises from the at sign in one place.

Working in active forms in Pafe where we have the at in front of report row formula i.e.

=@TM1RPTROW($C$11,"Server:DimName",,,"Description",,$D$21)

If I edit (f2, return) this then Excel decides that this is an email address and super helpfully changes the format of the cell and adds a hyperlink out to the default mail client.

Thanks Clippy!

So not only have M$ decided to add the @ to all the formula, that functionality is tripping up another "wizard".

Any ideas how to turn this off? It's that time of the week when the repetitive strain of this is beginning to make the pot boil!

Re: @= in active sheet formula

Posted: Fri May 22, 2020 1:54 pm
by MarenC
Hi Steve,

I just removed the hyperlink from the cell and that seemed to sort it

Maren

Re: @= in active sheet formula

Posted: Fri May 22, 2020 2:10 pm
by Bakkone
I recommend everyone to turn off the automatic formatting of URLs and email in their excel proofing settings. This is just one more reason to why.

Re: @= in active sheet formula

Posted: Fri May 22, 2020 3:34 pm
by Steve Rowe
@Bakkone, must have missed this option, I did look, I'll look harder!
@MarenC, it just comes back on the next edit so Yes you have to remove it for sure but I was looking to stop it happening in the first place.

Removing the snail stops it from happening as well.

Re: @= in active sheet formula

Posted: Fri May 22, 2020 7:36 pm
by Alan Kirk
Steve Rowe wrote:
Fri May 22, 2020 3:34 pm
@Bakkone, must have missed this option, I did look, I'll look harder!
You know how MS likes to make life easy for you! They haven't buried it more than a couple of levels deep.

Options -> Proofing -> Auto Correct Options -> AutoFormat As You Type tab.

I do actually find this option useful more often than not for web links rather than addresses, but this is one in, all in.
Autoformat.png
Autoformat.png (49.97 KiB) Viewed 235 times