Dynamic List of Dimension Elements in Websheet

[rturpyn]

I'm trying to create a data validated field in Excel that uses a dynamic range as a list of dimension elements.

I've followed the instructions from this thread: http://www.tm1forum.com/viewtopic.php?f=3&t=4836

I have this working in Perspectives, but not in TM1 Web or TM1 Application. The drop down list in Web and Application displays "#ERR!" while in Perspectives I can see the correct elements. I've narrowed the problem to the use of a dynamic range as my list. If I create a static named range the drop down list works both in Perspectives and the Web/Application.

I have a worksheet, "Lists", where I've created an index column and an element column.

The code used to create the element column is:

Code: Select all

=SUBNM("KPM-1011-10:Projects HY","Projects HY",A1)

As items are added to this dimension, this column is updating properly.

This is the code used for the dynamic range:

Code: Select all

=OFFSET(Lists!$B$1,0,0,COUNTIF(Lists!$B:$B,"?*"),1)

Attached are screenshots of what I'm seeing from within TM1 Application. The 3rd screenshot is the Lists worksheet as seen from TM1 Application, you can see it's populating correctly there as well. It all seems to come down to the named range being created using the offset command.

Thoughts or ideas? Thanks!

[PlanningDev]

VBA doesn't work in TM1Web so the code you have to update your named range isn't working in the web.

[Wim Gielis]

VBA doesn't work in TM1Web so the code you have to update your named range isn't working in the web.

What VBA code do you refer to?
OFFSET is a worksheet function in Excel.

rturpyn

Does it work if you do not use a complete column B? And restrict to a sensible number of cells in there?
(I have no TM1 Web installed ATM, so this is more of a guess)

[lotsaram]

As you are using 10.1 I don't think there is any need to be using Offset to create a dynamic validation range. If the source of the dynamic list is pre-existing subsets (either static or dynamic) then you should be able to do this with a picklist no problem. Then you will definitely have something that works in cube viewer, Excel and web. Note that you will need to use a picklist cube not picklist attribute as you will be making the picklist dynamic via rules in the picklist cube.

[AmbPin]

Hello,

I use a similar technique occasionally, however I use the COUNTA function instead of COUNTIF which seems to work on the web.

[rturpyn]

Does it work if you do not use a complete column B? And restrict to a sensible number of cells in there?

Hello,

I use a similar technique occasionally, however I use the COUNTA function instead of COUNTIF which seems to work on the web.

COUNTA works with a restricted range. COUNTIF doesn't work with a restricted range. I had avoided COUNTA thus far because in Excel the drop down contains blank rows for all of the unpopulated items in the range. However, when you open the Websheet in TM1 Application the drop down doesn't contain those blank rows. Seems odd but I'll take it.

The range doesn't have to be all that restricted, just defined. You can't use the entire column. In my test I used cells B1 - B5000 and it worked with COUNTA but not COUNTIF.

As you are using 10.1 I don't think there is any need to be using Offset to create a dynamic validation range. If the source of the dynamic list is pre-existing subsets (either static or dynamic) then you should be able to do this with a picklist no problem. Then you will definitely have something that works in cube viewer, Excel and web. Note that you will need to use a picklist cube not picklist attribute as you will be making the picklist dynamic via rules in the picklist cube.

The source is a pre-existing subset. I actually went this route after posting my initial problem. This works without issue, I was simply trying to avoid creating additional objects such as picklist cubes, and dimensions to contain the picklist, etc.

Thanks for the help.

[lotsaram]

The source is a pre-existing subset. I actually went this route after posting my initial problem. This works without issue, I was simply trying to avoid creating additional objects such as picklist cubes, and dimensions to contain the picklist, etc.

I'm glad you got it working. From my POV my preference would be to create such "additional objects", better in my mind to do it once on the server and have the validated data entry available in whatever UI and defined and maintained centrally once as opposed to creating the validation in the UI and potentially having to create and maintain it individually in every spreadsheet or websheet where it might be needed.