PickList is not visible with IFERROR formula

Post Reply
dharav9
Posts: 35
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 » Tue Sep 10, 2019 5:43 pm

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: 2152
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.7
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: PickList is not visible with IFERROR formula

Post by Wim Gielis » Tue Sep 10, 2019 9:09 pm

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

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 109 TM1 articles and a lot of custom code
Newest blog article: Set up a TM1 service with AutoHotKey

Post Reply