Dynamic drop down list on TM1 web

[wangspock]

I just start to learn TM1 Web. I need to create a input sheet that has a dynamic drop down list base on the user credential. I am using Excel data validation. It was working fine in Excel but get a #CIRC! on the list of selection when I move it to WEB. I try several different ways as I discover some of the Excel function does not supported in WEB. Below are the names contain formula. Data Validation Selection = List_BU_User

List_BU_User=INDEX(TB_BU_User,1,Sub_TB_Column_Numb) : INDEX(TB_BU_User,COUNTIF(Sub_TB_Column,"?*"),Sub_TB_Column_Numb)
List_Column_Numb=OFFSET(BU_Pick!$A$7,0,0,COUNTA(BU_Pick!$A$7:$A$201),1)
List_Users=OFFSET(BU_Pick!$B$7,0,0,COUNTA(BU_Pick!$B$7:$B$201),1)
Sub_TB_Column=INDEX(TB_BU_User,,Sub_TB_Column_Numb)
Sub_TB_Column_Numb=VLOOKUP(User_TM1,TB_Vlook_Column_Numb,2,FALSE)
TB_Vlook_Column_Numb=OFFSET(BU_Pick!$A$7,0,0,COUNTA(BU_Pick!$A$7:$B$201),2)
TB_BU_User : Excel table


Any help is greatly appreciated

[jim wood]

Why don't you build a pick list cube and use rules to build the pick lists? That way it'll work in any interface.

[tomok]

Why not store the drop-down data in a cube and make it a picklist? If you use the }Clients dimension in the cube you will then be able to pull back the appropriate selections based on their login ID.

[wangspock]

Person who manage TM1 does not want to create any additional object. I can only work with what I have.

[tomok]

Person who manage TM1 does not want to create any additional object. I can only work with what I have.

In that case I suggest you consult the TM1Web documentation. It has a list of supported Excel functions. As long as you stay within those boundaries then whatever you come up with in Excel will work in TM1Web.

[lotsaram]

Person who manage TM1 does not want to create any additional object. I can only work with what I have.

That is like saying "person who manage TM1 does not actually want this requirement met"
The best way (by far) to solve the requirement is to use a picklist cube. It is completely irrelevant that this another TM1 object.

[wangspock]

Finally figure it out. Lookup table does not work on TM1 Web.

[EvgenyT]

That is like saying "person who manage TM1 does not actually want this requirement met"
The best way (by far) to solve the requirement is to use a picklist cube. It is completely irrelevant that this another TM1 object.

Or more like saying person who manages TM1 does not know what is he / she doing...

I would strongly advocate to keep bare minimum in Excel and shift all the "funky" logic to TM1 where it is handled much better.
Plus creating a picklist cube is not really creating an additional object per se, its a control object.