@= in active sheet formula

Post Reply
MarenC
Posts: 110
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

@= in active sheet formula

Post by MarenC » Mon Dec 02, 2019 4:32 pm

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

Wim Gielis
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: @= in active sheet formula

Post by Wim Gielis » Mon Dec 02, 2019 11:23 pm

Hello,

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

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

dymoy
Posts: 16
Joined: Mon Dec 08, 2008 8:36 pm

Re: @= in active sheet formula

Post by dymoy » Mon Jan 06, 2020 11:47 pm

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.

Wim Gielis
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: @= in active sheet formula

Post by Wim Gielis » Tue Jan 07, 2020 8:04 pm

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.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

User avatar
Steve Rowe
Site Admin
Posts: 2062
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: @= in active sheet formula

Post by Steve Rowe » 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?

Cheers and Good Health too all.

User avatar
Alan Kirk
Site Admin
Posts: 6163
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: @= in active sheet formula

Post by Alan Kirk » Sat Mar 28, 2020 2:34 am

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 706 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.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

User avatar
Steve Rowe
Site Admin
Posts: 2062
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: @= in active sheet formula

Post by Steve Rowe » Mon Mar 30, 2020 8:52 am

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.

User avatar
Steve Rowe
Site Admin
Posts: 2062
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: @= in active sheet formula

Post by Steve Rowe » Fri May 22, 2020 1:20 pm

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!

MarenC
Posts: 110
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: @= in active sheet formula

Post by MarenC » Fri May 22, 2020 1:54 pm

Hi Steve,

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

Maren

Bakkone
Posts: 112
Joined: Mon Oct 27, 2014 10:50 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2013

Re: @= in active sheet formula

Post by Bakkone » Fri May 22, 2020 2:10 pm

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.

User avatar
Steve Rowe
Site Admin
Posts: 2062
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: @= in active sheet formula

Post by Steve Rowe » Fri May 22, 2020 3:34 pm

@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.

User avatar
Alan Kirk
Site Admin
Posts: 6163
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: @= in active sheet formula

Post by Alan Kirk » Fri May 22, 2020 7:36 pm

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 486 times
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

Post Reply