Substituing a Variable in Excel with SUBNM

[cbergen]

I am trying to change an excel template that is used in our planning process.

What I want to happen is for the user to be able to double click on the Account cell and a list of accounts related to their cost center will appear. They can then click on the account they want to plan for and enter in information in the input section of the sheet.

Currently each user has a defined Subset that lists the accounts that are relevant to their needs.
In my attempt to work this out I first used a SUBNM formula and hardcoded the relevant subset in the formula
ex.
=SUBNM("applixtm1:Accounts","2010_PLAN_COSTCENTERG","","Description")

When I double click on the account cell with this formula the list of accounts related to subset 2010_PLAN_COSTCENTERG appears.

So far so good if I wanted to hardcode this formula variations on 30 to 40 different templates instead of having one template.

Next thing I tried was to conconcentate a formulate that would specifiy the subset from other cells on the worksheet that already contains parts of the name of the subset.

ex
=SUBNM("applixtm1:Accounts",$P$7&"_"&$P$6&"_"&$L$10,"","Description")

Where P7 is 2010, P6 is PLAN and L10 is COSTCENTERG

That did not work. A double click with this formula gave me the full account list.

I thought maybe something was wrong in the concencentate formula so I typed the name of the subset into a cell and linked the SUBNM to that cell. Same result, a double click yielded the full account list.

ex
=SUBNM("applixtm1:Accounts",X7,"","Description")

Where X7 is 2010_PLAN_COSTCENTERG and on another attempt "2010_PLAN_COSTCENTERG"

Does anyone know how this can be done with SUBNM or if another TM1 function would be better than the one I am trying?

Thank you

[George Regateiro]

From the Reference Guide

TIP: Do not use cell references as arguments with the SUBNM function. Cell references prevent the
function from correctly calling and launching the Subset Editor when you double-click the cell that
contains the SUBNM function.


Now that being said I have seen where it will work once the template is uploaded to TM1Web, but that would also be unsupported by IBM.

[Alan Kirk]

From the Reference Guide

TIP: Do not use cell references as arguments with the SUBNM function. Cell references prevent the
function from correctly calling and launching the Subset Editor when you double-click the cell that
contains the SUBNM function.

Now that being said I have seen where it will work once the template is uploaded to TM1Web, but that would also be unsupported by IBM.

The FAQ Links post is your friend. Specifically, the link that it provides to this thread re the changed functionality of that in 9.4.1.

Edit: However I note that the poster is on 9.1, where I wouldn't expect it to work at all; must test that out when I get to work...