Active Forms and Vertical Comparison in Excel

[cbergen]

Quick background I'm writing some Active Form Reports to replace some old Slice reports we have used for years to take advantage of the zero suppress capabilities.

On the current report I have a percent to sales column that calculates as a formula in the excel sheet against the sales line in the report.
Amt Percent
Sales 800 100.0
Exp 100 12.5

In active forms I noticed I can insert a column and insert a formula and that it will calculate correctly as long as the items I'm calculating are in the same row. If I try to add arguments from other rows I get Ref error. (EX C35/C36 becomes C35/#REF) Is there a way write an excel formula to pull data from the row above or below? I am thinking of alternatively either creating a % to Sales element in my measure cube or trying a DBRW formula in my excel calculation but I wanted to make sure there wasn't modification to an excel formula first.

Thanks

Chris

[declanr]

I have referenced the row above or below in active forms many times in a number of versions; so it should work.

What exact version are you using? (To rule out bugs - not seen it in any as far as I remember though)

And what is the exact formula (snapshot or just very descriptive comments as to the structure of the sheet etc)?

[lotsaram]

To refer to rows above/below that are within the active form row range you need to use offset or indirect. This is because all rows in the active form except for the first row are deleted and reinserted on each refresh which will give #REF errors with a standard reference.

[declanr]

To refer to rows above/below that are within the active form row range you need to use offset or indirect. This is because all rows in the active form except for the first row are deleted and reinserted on each refresh which will give #REF errors with a standard reference.

You can do direct references to the row above but since that is usually a title for the first row it's worth adding an if statement in to ignore the first row.
As lotsa has stated you need to change the formula up a bit for rows below; I've always gone with offset by force of habit.

[cbergen]

Thanks, I like the offset idea but I ended up using an DBRW formula as it made a easier way to anchor to the sales figures I needed for the % of sales formulas.


Thanks

Cbergen