kburrows wrote:Hi, new to TM1 and have a question about formulas within TM1 Web:
I need to dynamically populate a couple of cells within my workbook based upon data pulled from an active form (stored on another tab within the workbook). I have used the OFFSET command and named the results (Own_CC, CheckCC1, CheckCC2, CheckCC3).
The formula I am trying to evaluate is as follows:
Code: Select all
=IF(AND(CheckCC1<>Own_CC,CheckCC1<>0),CheckCC1,IF(AND(CheckCC2<>Own_CC,CheckCC2<>0),CheckCC2,IF(AND(CheckCC3<>Own_CC,CheckCC3<>0),CheckCC3,SUBNM(Server&":DeptCC","CostCenters",""))))
Basically, this formula says that if data exists in the CheckCC1-3 ranges, list that cost center. Otherwise, allow the user to choose a cost center with a SUBNM function.
This works in Excel, but when I upload to TM1 web the SUBNM functionality does not work. Has anyone successfully used this before or is there a better way to accomplish this?
Thanks in advance.
kburrows
There are certainly better ways to do this. I can't imagine why you need to pull element names from another worksheet using OFFSET but leaving that asside you will be able to get your SUMNM formula to work if you break the conditional logic into a separate cell and then feed the result into the SUBNM as the 3rd "element name" argument.
E.g. Define a new named range "CheckCCResult" with the formula
=IF(AND(CheckCC1<>Own_CC,CheckCC1<>0),CheckCC1,IF(AND(CheckCC2<>Own_CC,CheckCC2<>0),CheckCC2,IF(AND(CheckCC3<>Own_CC, CheckCC3<>0), CheckCC3,"")))
and your SUBNM formula then becomes
=SUBNM(Server&":DeptCC","CostCenters",CheckCCResult)
As OFFSET is now supported in TM1 Web there should be no reason why this won't now work.
However the issue I think Jim was aluding to in the web will still persist. Namely in TM1 Web SUBNMs that are formula driven by paramater cells will only stay "dynamic" (that is the subset member value refreshes when the parameter values change) until such time as the cell is clicked and the subset editor is launched. This has the effect of rendering the parametized argments static. The SUBNM will still update but only when clicked. This is different from Excel where the formula remains dynamic.
