Page 1 of 1

Removing TM1 formulae from a sheet

Posted: Fri Jul 13, 2018 5:23 pm
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.

Re: Removing TM1 formulae from a sheet

Posted: Fri Jul 13, 2018 6:10 pm
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.

Re: Removing TM1 formulae from a sheet

Posted: Fri Jul 13, 2018 8:06 pm
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