Removing TM1 formulae from a sheet

Post Reply
NPTM1
Posts: 13
Joined: Mon Sep 26, 2016 9:59 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2010

Removing TM1 formulae from a sheet

Post by NPTM1 »

Hi

I've been using a macro that I found online to replace TM1 formulae with values. It searches for the formulae beginning with "=DBRW" and "=-DBRW" and various other combinations, which so far has been working great.

However I've now got DBR within IF formulae, and the true/false conditions will change for each cell (leaving and I can't account for every combination within the macro).

Does anyone have any tips on how I can get around this?

I thought about "tagging" the start of each formula with =N("TM1")+ which would return a 0 value, and then having the DBR formula after it, However this only really works for numbers. So something like:

=N("TM1")+IF(A1=B1,DBRW(XXXXX),"OK")

would return 0OK. Also thought about =IF(N("TM1")=0,"","")& but this will only work for strings and not numbers.
Wim Gielis
MVP
Posts: 3098
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Removing TM1 formulae from a sheet

Post by Wim Gielis »

Usually, you should ONLY use:

=DBRW( ... )
and
=DBR(

and that's it.

No funny things like: * 1,000 at the end, and no - sign in front, no =DBRW( ... ) + DBRW( ... )
These manipulations should ideally be done in TM1.

Does this make life more difficult for your macro ? Maybe yes. But that's about it.
Use PAX to convert to hard values. Or extend your macro to check:

=DBRW(
=DBR(
=DBRA(
=DBSS(

Or, use ranges where you know you will only get TM1 formulas.

If you work in a consistent way, this should not be a problem.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
User avatar
Steve Rowe
Site Admin
Posts: 2407
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: TM1 v6,v7,v8,v9,v10,v11+PAW
Excel Version: Nearly all of them

Re: Removing TM1 formulae from a sheet

Post by Steve Rowe »

http://www.tm1forum.com/viewtopic.php?t=2515

I'm pretty sure the forums TM1 Tool add in as some code for stripping out TM1 formula when they are nested, its pretty old though and may not play well with PAx
Technical Director
www.infocat.co.uk
Post Reply