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.
Removing TM1 formulae from a sheet
-
- MVP
- Posts: 3122
- 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
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.
=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
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
- Steve Rowe
- Site Admin
- Posts: 2417
- 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
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
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
www.infocat.co.uk