TM1 Forum

jim wood wrote:Why don'y you just number a column say 1-10. Then use subnm to pull the element for that index and then use the subnm column column as data range???? I don't understand why this doesn't work??

lotsaram wrote:I think Jim has covered this but you could just have surplus index numbers and set the name range for your cell validation of the element names to be dynamic and grow as the non-blank list of members grows. This is quite easy to do with an offset formula.

If you want to use VBA you could also use SUBSIZ or DIMSIZ to get the number of elements then fill down and reset your ranges automatically with VBA.

ExApplix wrote:

lotsaram wrote:I think Jim has covered this but you could just have surplus index numbers and set the name range for your cell validation of the element names to be dynamic and grow as the non-blank list of members grows. This is quite easy to do with an offset formula.

If you want to use VBA you could also use SUBSIZ or DIMSIZ to get the number of elements then fill down and reset your ranges automatically with VBA.

I guess VBA is the way to go. I am just a newbie to VBA. Can someone guide me how to do that or share some sample VBA code which can do this?

Thanks

=OFFSET(Lists!$A$1,0,0,COUNTA(Lists!$A:$A),1)

Sub RecreateBUList()
'(c) Alan Kirk, 2011

Const SC_DIMNAME As String = "Planning Sample:Plan_Business_Unit"

Dim l_NumberOfElements As Long

'Clear the existing range. If there are no entries this will
'error, but we don't care so we just skip it.
On Error Resume Next
ThisWorkbook.Worksheets("Lists").Range("BusinessUnits").ClearContents
On Error GoTo ErrorHandler

'Get the number of elements in the dimension.
l_NumberOfElements = Application.Run("DimSiz", SC_DIMNAME)

If l_NumberOfElements = 0 Then
    Err.Raise vbObjectError + 20000, , "No elements found in the " _
     & SC_DIMNAME & " dimension. Check that you are logged in."
End If

'Fill a range, starting at "BusinessUnitsStart" (which I've defined
'as A1 on the Lists sheet) with SubNm formulas to cover the size of
'the dimension. These will then be picked up by the "BusinessUnits"
'dynamic range.
ThisWorkbook.Worksheets("Lists").Range("BusinessUnitsStart") _
 .Resize(l_NumberOfElements, 1).FormulaR1C1 = _
 "=SUBNM(""Planning Sample:Plan_Business_Unit"","""",ROW())"

ExitPoint:

Exit Sub

ErrorHandler:

MsgBox "Error " & Err.Number & vbCrLf & Err.Description

Resume ExitPoint

End Sub

tomok wrote:I know others on this site like VBA but my advice to you is don't use it. If you do then you can forget about ever using the report in TM1Web.

ExApplix wrote:

tomok wrote:My goal is to populate a ComboBox and then use the spreadsheet over the TM1Web.

tomok wrote:Create the new range in Excel and use the following in the Refers To box: =OFFSET(Lists!$A$1,0,0,COUNTIF(Lists!$C$1:$J$100,">0"),1). Now you can use data validation in Excel and use the range ValidChoices. It will be a drop-down of only those rows for which a subset member exists.

jim wood wrote: I agree with you to a degree. I don't however see TM1 web as the only reason not to use it. While VBA does have it's place in making things automated etc. I feel that using it for simple dimension element list is like using a sledge hammer for a small tack. But that is just my humble opinion,

Alan Kirk wrote:The other is to generate just the lists that you need via VBA.

Alan Kirk wrote:For a single dimension, maybe. But supposing that you had (and I have had) several dimensions of hundreds to thousands of elements each.

jim wood wrote:

Alan Kirk wrote:For a single dimension, maybe. But supposing that you had (and I have had) several dimensions of hundreds to thousands of elements each.

That's when you do need the sledge hammer. As you stated (as requested originally) it is a bit much for a a single dimension.