Two active forms alongside

[mvaspal]

Hi

Related to my previous post, I am interested if anyone could ever come up with a good solution for displaying 2 active forms alongside each other, that also works on the web. Actually, we need to create 3 active forms on the left hand side below each other, and 3 other on the right hand side below each other.


Until now we found two solutions, both work relatively ok, but not perfect:
1. The "left" active form is a real active form, and the "right" active form is a linked picture from another sheet that contains the real active form. This works quite nice in Excel, not on the Web.

2. We create all active forms below each other on one sheet, and on the final sheet we reference these with the INDEX formulas and so we can have the reports alongside each other. The only issue is that the number of rows may change in the active form and so on the final static sheet there may be empty rows because we need to do formatting assuming the maximum number of rows (this is not so bad nr of rows change between 10-25).


So any good solution for this requirement that works on the web?

Thanks!
Matyas

[tomok]

The only issue is that the number of rows may change in the active form and so on the final static sheet there may be empty rows because we need to do formatting assuming the maximum number of rows (this is not so bad nr of rows change between 10-25).

You can "hide" the empty rows by using conditional formatting. Just have conditional formatting turn the cell contents to white on white (or whatever your background color is) and remove all other stuff like borders, etc., when the cell formula returns nothing or zero.

[mvaspal]

Hi
Thanks tomok, great idea, this is exactly what we did! The only problem is that we need 3 reports on the left and 3 on the right and if we have blank rows after the second report, it does not look too good. I mean the conditional formatting allows us not to show anything but the blank rows are still there.
It would be easy to remove these cells with a macro but we need tm1 web...
Or do I miss anything from your comment?

Btw, if we do not find a better solution we will go most probably with the one you also recommend

Thanks
Matyas

[tomok]

Just speaking off the top of my head (i.e., I haven't tried this specifically) but you can write formulas to get row counts and the starting and ending rows for each active form. Using this couldn't you write nested IF statements that would determine which active form and row to pull into your "Report" tab?

[mvaspal]

Hi
Yes, we have also tried that but when trying to use an INDIRECT formula, we have run into this:
https://www-304.ibm.com/support/entdocv ... wg1PI46526

INDIRECT does not seem to be working on the Web.


Anyway, we keep trying according to your suggestion, maybe we can make it work, thanks!

[TomBr]

Hi,

As you mention you cannot use the Excel function INDIRECT on TM1Web. However, from memory, you can use the Excel function OFFSET on TM1Web to achieve the same result.

Tom