TM1 Forum

Hi,

I'm working in 9.5.2 with FP1 and excel 2007.

I have created a TM1 worksheet called Menu with drop down lists for end users to select Country/Region/Project. The source of these drop down lists are SUBNM formulas. All works fine. I have added a TM1 action button to open another worksheet called Actuals. The options chosen on the action button are:

Go to another Worksheet
Calculation = None
Match title elements = false
Replace current workbook = True

The Actuals worksheet contains an Active form with DBRW's - this worksheet is for review only, no inputting form the end user.

When I click on the Action button from the menu I get an error message "Type Mismatch" and then excel freezes and I have to close down and re-start. Neither worksheet is attempting to write data back to a cube, let alone write string data to numerical fields (or visa versa). I have saved the Menu worksheet with automatic re-calc ON (so that the drop down lists refresh). The actuals worksheet as automatic re-calc switched OFF (as there may be lots of DBRW's). I have tried the action button with both worksheets having Recalc on - I still get the same results so I don;t think it is this.

I have added an Action button to the Actuals worksheet to open Menu with the same settings and it works fine with no error message.

Both worksheets are designed to be accessed through TM1 Web - I get the same error message in TM1 Web as I do in excel.

Any suggestions as to why I'm getting the error message/what it means/how I fix it would be much appreciated.

Cheers

tosca1978 wrote:I have created a TM1 worksheet ... with drop down lists ... The source of these drop down lists are SUBNM formulas ... I have saved the Menu worksheet with automatic re-calc ON (so that the drop down lists refresh)

This reliably crashes Excel for me (Excel 2007/ TM1 9.5.0). Whilst it seems a bit of a pain, leaving automatic re-calc off and using Shift+F9 to refresh the SUBNM lists is much more stable. As a rule, you shouldn't have automatic re-calc on when using the TM1 add-in.

I would also test setting the 'replace current workbook' option to false.

Hi rmackenzie,

thanks for your post.

I have switched excel calc back to manual (I normally always have this on manual using TM1 - my client doesn't like the Alt+F9 approach but ALSO doesn't like the idea of having to click an action button that does a TM1 refresh in between each drop down list selection - hence I was experimenting with auto re-calc just for the menu screen).

I have also tested with "Replace this workbook" to false. I am still getting the "Type Mismatch" error and then excel crashes.

Cheers

Why use a drop down at all. Replace the drop down by putting the SUBNM formula in the cell you want the user to make a selection in. Although it won't be a drop-down, it will still act as a selection tool (when the client double clicks on the cell). If you configure the formula properly you can limit the list of elements to show just what you want. I almost never use drop-downs unless I need a selection that's not something stored in a dimension.

Hi Tomok,

[quote="tomok"]If you configure the formula properly you can limit the list of elements to show just what you want.

I have thought of using SUBNM instead of a drop down list - however I'm not sure how to configure these to show exactly what I want!

I have three drop down lists:

Country - once one is selected displays a list of regions within that country which is the source of...
Regions - once one is selected displays a list of projects within that region
Projects - select a project

If i have a SUBNM formula such as =SUBNM("tm1_Budget:Projects","Country","UK") where "Country" is a subset, the following formula needs to be =SUBNM("tm1_Budget:Projects","UK","00007DEF") where "UK" is another subset for the next formula to use etc.

I have tried making the subset element of the SUBNM formula reference another cell eg =SUBNM("tm1_Budget:Projects",R12,"00007DEF") where cell R12 contains the first SUBNM selection however this does not seem to work.

If you have any suggestions on how to get the most from the SUBNM formula I would really appreciate it.

Many thanks

Hi all, I just though that I would post an update:

After some testing I have found out why I get a "Type Mismatch" error message:

I had a SUBNM formula referencing another cell (the ELCOMP formula). Once I made this SUBNM formula static I stopped getting the Type Mismatch error message.

I actually only had the SUBNM formula in the sheet so that I could send it's value as a parameter via an Action button to a named range on another worksheet. I have removed the SUBNM formula all together and I have selected "Value" as the Source Type (in advanced option within the Action button properties) and referenced the cell with the ELCOMP formula.

All seems to work very well now.

Thanks for all your help.