Index Column or ROW() and placement in active form

[tosca1978]

Hi all,

Using 9.5.2 + excel 2007.

I have an active form (which will be accessed via TM1 web). It has some selection combo boxes working off ELCOMP formulas for user to select criteria based on dynamic lists. I thought that this had been working fine for a few weeks now but actually spotted an error.

The elcomp formula is: "=ELCOMP("tm1_TC:JV Measures",$IN$1,IN3)", where "IN3" is an index column - numbers 1 to 300 in hardcoded running down the column. This tells the ELCOMP formula which index number on the Dim to bring back.

I have noticed that where the Active form rows kick in (row 25 on this active form) the sequential numbers in column "IN" are overwritten, in this case by the number "23" in rows 25-35 which is where the active form stops after rebuild.

I think this is because the TM1RPTDATARNGARPT1 formula refers to rows !$25:$35 which will repeat the formula down in each column from row 25. This makes perfect sense for the active form columns however it ruins my index numbers and then my ELCOMP formula not to mention 2 other columns to the right of the active form where I have hardcoded two lists of data used as the source of 2 more selection combo boxes.

I tried editing the TM1RPTDATARNGARPT1 to "!A$25:IK$35" using the Name Manger in excel. It lets me save it but when I open Name Manager again it has reverted back so it didn't really save.
I have also tried making the index column a formula IN4=IN3+1 etc. So that if the formula gets copied down it would still be the correct number. But this resulted in =#REF!+1 at row 36.

I know that Active forms owns the rows and you can use the columns. However, I wondered what the best practice was around this. I'm sure that I read that it was better to use an index column rather than ROW() eg: "=ELCOMP("tm1_TC:JV Measures",$IN$1,ROW())". Should I avoid using the columns to the right of the Active Form all together? The problem is that I didn't want to insert 300 rows above the active form for the index etc and hide them. And if I wanted the index below the active form I would have to put it WAY below just to make sure nothing got overwritten by the active form if expanded out. And considering that the solution is to be in TM1 web I wanted to avoid too many hidden rows (only 6 columns to hide as opposed to 300 rows).

If anyone uses similar approaches to active forms etc and have found a good way of setting these up I'd appreciate the advice.

Cheers

[lotsaram]

Normally you would manage this by having the range to fill the combo box or data validation list on a separate worksheet.

[tomok]

The problem here is that you need to create a formula that won't break when it is copied down so the "+ 1" thing can't refer to the previous row. Each row inside the Active Form is blown away when the active form is rebuilt so you need to structure the +1 logic to not refer to the previous row. Here is what will work:

1) In any row outside and above the active form and in the same column as where the ELCOMP formula will be put the following formula (we are going to assume cell IN23): =ROW(IN23).
2) In the first row of the Active Form, in same column, put this formula (using your example): ELCOMP("tm1_TC:JV Measures",$IN$1,ROW(IN25)-$IN$23)

When your active form rebuilds the ROW(INx)-$IN$23 formula will return an index number starting with 1 and incrementing by one for each column.

[lotsaram]

The problem here is that you need to create a formula that won't break when it is copied down so the "+ 1" thing can't refer to the previous row. Each row inside the Active Form is blown away when the active form is rebuilt so you need to structure the +1 logic to not refer to the previous row. Here is what will work:

1) In any row outside and above the active form and in the same column as where the ELCOMP formula will be put the following formula (we are going to assume cell IN23): =ROW(IN23).
2) In the first row of the Active Form, in same column, put this formula (using your example): ELCOMP("tm1_TC:JV Measures",$IN$1,ROW(IN25)-$IN$23)

When your active form rebuilds the ROW(INx)-$IN$23 formula will return an index number starting with 1 and incrementing by one for each column.

That will fix the issue of the correct ELCOMP values being filled in the list area once the active form rows start. But unless the range for the combo box selection is defined as a dynamic named range using Index and/or Offset formulae then the fill range for the combo box will still get truncated when the active form is rebuilt. Unless the designer understands how to build such a named range a separate worksheet is safer (but this can have its own issues if it requires separate recalculation to change the selection list values.)

[tosca1978]

Tomok/Lotsaram,

thanks both for your replies.

Tomok's formula works and fix's the elcomp formula - great.

Lotsaram - I misunderstood what you meant by "named range" - I though that you were referring to simply naming the range and using this as the source of the combo box. Now I know you are referring to using OFFSET and INDEX. I actually already use this method in another part of my model for the source data of a graph (so that the graph is dynamic and changes when TM1 data as it's source is refreshed - as opposed to just dissappearing if based directly on source data). I didn't think to use the same method here...so thanks for reminding me!

Thanks both...

[tomok]

That will fix the issue of the correct ELCOMP values being filled in the list area once the active form rows start. But unless the range for the combo box selection is defined as a dynamic named range using Index and/or Offset formulae then the fill range for the combo box will still get truncated when the active form is rebuilt.

Yes, I always create range names for data validation and/or combo boxes. In this case all you need to do is to extend the range, when creating the range name, to one extra row beyond the active form range and the range will expand as the active form is built. The only caveat is that you will have one empty item at the bottom of your list. NBD.