PickList is not visible with IFERROR formula

Post Reply
dharav9
Posts: 72
Joined: Wed Aug 15, 2018 3:18 pm
OLAP Product: TM1
Version: 10.3
Excel Version: 2016

PickList is not visible with IFERROR formula

Post by dharav9 »

Hi, All

While creating input sheet for the users, i found that if i use DBRW formula along with IFERROR formula then my cells does not show picklist drop down.
If i just write the DBRW formula then it shows picklist.
If i just apply the DBRW formula then all cells without having corresponding value would throw #Value! error (as expected) in column. I want to restrict that to make presentation clean.

E.G.:
DBRW(Cube, D1,D2,D3,D4) = Works fine if all D1,D2,D3,D4 Values are present in the sheet
DBRW(Cube, D1,D2,D3,D4) = throws #Value! if all D1,D2,D3,D4 Values are not present in the sheet (Expected behavior as user would fill out these values as needed)

to overcome #Value error stated above,

I tried =IFERROR((DBRW(Cube, D1,D2,D3,D4)),"") => did not bring picklist drop down (where D1 to D4 values are present)
I also tried = IF($A3="","",DBRW(Cube, D1,D2,D3,D4)) => did not bring picklist drop down (where D1 to D4 values are present)

Is there a method to populate pick list if all values of D1 to D4 are presents as null ("")? or any workaround (I cannot put manual list as pick list values may change as business unit requires)
Please feel free if you require further information.

Thank You
Dharav
Wim Gielis
MVP
Posts: 3105
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: PickList is not visible with IFERROR formula

Post by Wim Gielis »

You should not wrap the DBRW formula for the picklist in another function.
Even more, you should NEVER wrap ANY DBRW formula in any other function.

I would use some clever conditional formatting to hide or show the picklist / DBRW.
For example, use DIMIX on the elements such that only if all DIMIX'es are strictly positive, the picklist is shown.
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
Post Reply