TM1 Forum

Hi,

I am trying to use a VLOOKUP function in a Websheet, Basically I have a hidden sheet where I have a list of Measures and their Excel Format Code, such as:
Account Balance #,##0_);(#,##0)
Interest Rate 0.0000
.......etc.........

So in my Main sheet, I have an Active Form with dynamic columns ( based on a user selection, different columns show. There is a 2-dimensional cube with 2 dimensions, AccountType and AccountMeasures populated with 0/1, so if a certain Measure is applicable for an Account Type, the value is 1, otherwise 0. Then I have an MDX subset that returns the list of APplicable Measures for the selected AccountType. So then in my ActiveForm Column Header, I use a SUBNM function to retrieve the elements of subset, by applying INDEX=1 for first column, INDEX=2 for second column etc. This is working fine, so the activeform is showing the Measures that are applicable for the selected AccountType.
But because the number or Order of Columns in the active form change, I cannot apply the formatting in the hidden area of active form.

So I am trying to format the ActiveForm Cells by embedding the DBRW formula with the excel TEXT function ( =TEXT ( DBRW ( cubename, el1, el2, .... , elmmeasure ) , MeasureExcelFormat ).

The challenge I am having is, how to pull the Measure Excel Fomat. So i created an attribute on the Measures DImension and then tried to pull the attribute value with DBRA function but it looks like TM1 is not liking that, since I already in my DBRW formula of the Active Form, I am getting the Measure Name through the SUBNM formula, and it looks like TM1 cannot afford an additional layer of nesting TM1 formulas to retrieve the Measure Attribute.

Since the above workaround is not working, I am trying to keep the Measures List with their attributes in a separate hidden sheet and trying to use VLOOKUP function to get the Measure Excel Format. It is working fine in Perspectives, but as soon as I publish it to TM1Web, all the VLOOKUP cells return #N/A

VLOOKUP function is listed as a supported function.

I am using TM1 10.1.1 and Excel 2003

Any idea about how to make this work will be much appreciated

It's a little hard to follow, but you should be able to nest functions as much as you want. In general, when I've come across situations where a cell worked in Excel but not in TM1 Web, I've gone through my formulas with a fine tooth comb and found something that shouldn't have worked anywhere but somehow did in Excel. Think: using a DBRW when I needed a DBR for precedence, over-filtering in the VIEW/TM1RPTVIEW, etc. Don't get hung up on the VLOOKUP, trace back a level or two.

VLOOKUP only works in the web when you limit the range. Looking up in an entire column does not work.

For me it worked even not with a restricted (named) range.
TM1 10.2.0 FP3, and, indeed, it is the german version of Excel 2013 I use for development.
But on the server is installed english version of Excel 2010.
Could the different versions of Excel cause additional malfunctions?