Cell Reference in SUBNM

[kielmc]

So...the documentation clearly states that we cannot use cell references within a SUBNM function...does anyone have any workarounds?

[Alan Kirk]

So...the documentation clearly states that we cannot use cell references within a SUBNM function...does anyone have any workarounds?

I'm fairly certain that the only workaround you can do will involve some VBA. I'm not sure what you're trying to do (i.e. what part of the formula you need to be a cell reference), but let's say that you want to be able to have the user specify the server name in a cell.

Over the top of the cell(s) which contains the SubNM(s), you could put a transparent shape object(s) with macros assigned to them.

The macro will intercept the user's click; it could then update the formula of the relevant SubNM with the server name that the user has entered, select that cell, then use the
Application.Run Range("[tm1.xla]tm1!ENTER")
(or equivalent for your version) macro to trigger the SubNM action.

In other words, you may not be able to use a range reference directly, but you could have VBA update the SubNM formula so that it contains the contents of the range that you want to refer to.

[kielmc]

Thanks Bourne ; )

The full story is that I need to add an element to a dimension from an excel workbook published over <cough, mumble, whimper> TM1web </cough, mumble, whimper> . Each element added has a number of attributes associated with it that need to be specified when created. The available choices for each attribute depend on those previously picked. For example

Element to Add: ColorsAreCool
1st Attribute: Color (choices are Red, Green, & Blue)
IF the first attribute is Red, options for the second attribute would be Pink, Burgandy & Persimmon.
If the first attribute is Blue, options for the second attribute would be Azure, Sapphire, & Periwinkle (my personal favorite)

I've done this before using VBA in an excel workbooks, but now I'm attempting to use <cough, mumble, whimper> TM1Web </cough, mumble, whimper>

so, i thought it'd be cool if i could create dimensions that represent the attribute options and then subsets wihin each dimension that i could reference in each subsequent SUBNM()...alas...i can't reference anything within the subnm() and i can't use VBA in tm1web...

-Mark

[ScottW]

Mock TM1 Web all you like but one thing that a TM1 websheet will do that the Excel client will not is parse cell references and invoke the subset editor popup, try it

Of course there is the major drawback that you have to build the template in blind faith as this behaviour doesn't exist in the Excel "report building interface."

And there is one major caveat
Each SUBNM reference is effectively "dynamic for single use only." After the SUBNM arrow is clicked and you get the popup this triggers the evaluation of the SUBNM dimension reference which is then effectively hardcoded to the cell as a string reference. That is if the precursor parameters change after and the SUBNM is re-selected then it will still point to the first dimension.

I haven't ever tested to see if it is just the first cell reference argument (server:dimension) that is effectively hardcoded upon selection, or if this also applies to subset names and index nos that might also be cell refs, but the experiment woudl be easy enough to do.

Its not ideal, or even that great, but with a little care in the layout of your form it might just be workable in TM1 Web!

[Kerry372]

I use cell references in subnms to update the default months on my tm1web files. For example subnm("months","dashboard_months",D4) where D4 is the referenced cell that dynamically changes to the last closed month. They can still pick a different month, but the sheets always default to the last closed month.

Maybe I'm just confused about this topic

[Alan Kirk]

Mock TM1 Web all you like but one thing that a TM1 websheet will do that the Excel client will not is parse cell references and invoke the subset editor popup, try it

This is most interesting indeed... I might play around with that and see what can be done.

Provided of course that this isn;t an "undocumented feature" that vanishes in version 9.4...

[Eric]

Mark my words if it isn't 9.4 it is only a matter of time, documented or undocumented it won't matter they will either take it away or call it a new module and charge for it.

[Mike L]

1) So... why doesn't it work in Excel? Because if the dialog replaces the reference with the chosen element then the original reference is lost. Save the workbook and your carefully constructed formula reference is gone. The solution is to check whether the cell has references (easy to check in the Excel object model) and disable the pick function.

2) So... why does it work in web? Whether by design or by oversight, the safety check is not needed because a web user can not save the workbook. The carefully constructed formula reference is not at risk in the master template.

[Alan Kirk]

1) So... why doesn't it work in Excel? Because if the dialog replaces the reference with the chosen element then the original reference is lost. Save the workbook and your carefully constructed formula reference is gone. The solution is to check whether the cell has references (easy to check in the Excel object model) and disable the pick function.

2) So... why does it work in web? Whether by design or by oversight, the safety check is not needed because a web user can not save the workbook. The carefully constructed formula reference is not at risk in the master template.

That may indeed be the reason, and it's a good point.

However in the case of Excel it's only really a problem if the THIRD argument is the cell reference. Such an approach would probably be more an exception than a rule; though admittedly it's an exception that was mentioned in this very thread. (I imagine that the template in that case was read only, which overcomes the corruption problem. Edit: Well, that and the fact that it's used in Web rather than Excel, duh...) In the majority of cases I can imagine that the server name or subset name would be the one that it would be useful to make "dynamic".

But you're right; if Iboglix is indeed merely checking whether there are references and disabling the pick functionality accordingly, changing it to check whether the THIRD argument alone is a reference would be a non-trivial change to the function.

And we know what happens whenever there are non-trivial changes to the functionality in TM1, at least until the following point release or three comes out.



Heeey, I just realised! You're here!!! Does that mean that you've broken the "No work at home" rule, your employer has relaxed "the rules", or you've found yourself a new TM1 home? Whichever, welcome aboard!