Ok. I'm making progress.

I tried: =IF(1, "FormatHidden", "FormatHidden") to try to force hiding in all conditions. All that happens is columns A,B&C show blank and the rest of the columns are unaffected.

Even if I could get the entire line to show blank would I not simply see a lot of blank lines on my printed report?

Is the "FormatHidden" style supposed to suppress printing of the line? I can't find in the manual where it describes what this style specifically means.

Thanks again.

Richard

Hi Richard,

'FormatHidden' is one of the pre-defined Styles that you can see from the Format menu and 'Styles'. Essentially, this is pre-defined cell formatting. This hides the row, not just blanking it out. Not only from printing but also viewing.

The 'IF' formulas I used as examples in the prior post typically require some sort of condition in conjuction with 'FormatHidden'. When you hid everything, that could have confused things - if everything is hidden, you won't see anything. You could try a different format such as 'HighlightRed' to get a more visual feel of what is happening.

If you are trying out this 'FormatHidden' formatting, I recommend doing it first without '.RowFormat' in the top-right cell. This allows you to see which rows will be hidden (and which won't be) if .RowFormat was present.

The Help file within the Financial Reporter may give more information that the manual might. In the 'Advanced Report Features' section of the help file (on the home page), there is a link for Row Formatting, which describes this function.

I see what you mean by removing the .RowFormat function and when I use "HighlightRed", the behavior is exactly as I would expect. Without .RowFormat, I see "HighlightRed" in the far right column of every generated row.

I'm using =IF(1, "HighlightRed", "") which I assume logically asks the question "If any cell in this row has a non-zero value the apply the "HighlightRed" style.

Now when I change "HighlightRed" to "FormatHidden": If I remove .RowFormat, I do see "FormatHidden" in the far right column of every generated row as expected. But, with .RowFormat, the strange thing is that the rows are not hidden at all. What happens is that columns A,B&C are blanked out while the rest of the columns remain unchanged. "HighlightRed" applies to All columns in the row but "FormatHidden" seems to only apply to columns A,B&C and would be more aptly called "FormatBlank".

Does this make sense?

Thanks.

Richard

Hi Richard,

=IF(1, "HighlightRed", "") will put "HighlightRed" in the cell regardless of anytghing else, since "1" is always TRUE. If you want to check whether a group of cells are equal to zero, then test =IF((A1+B1+c1+d1)=0,"HighlightRed","") - add the cells up and see if the result is still zero.

I think that we're missing something simple here. If you ZIP up your data and send it to me in an e-mail with the name of the statement that's giving you a problem, then I can take a look and see what it might be. Once a row gets the attribute "FormatHidden", it can be hard to remove and may cause unexpected results. Zero suppress is normally the best way to proceed.

Also, was this statement started by dragging and dropping account ranges from the group tree, conversion of a DOS specification, editing a sample specification or just starting from scratch and typing formulas.

Hi Andrew,

Just emailed a zipped file to techsupport@softrak.com with your name in the subject line.

Thanks again.

Richard

If you change formatting on the cell, this overrides any conditional RowFormat formatting of the same 'type'. For example, if you specifically set a cell to have blue font, this will overrride any .RowFormat format that would change the font colour, but cell background colour or bold/italic font would still work.

FormatHidden is essentially setting the row height to zero. What probably has happened is that you have specifically set the row height and this is overriding the FormatHidden. It's possible that the original testing by setting all rows to FormatHidden has caused the scenario of giving unexpected results now.

Andrew is right that using the ZeroSuppress button in the toolbar is a better (and easier) way to simply hide accounts that have all zero amounts.

There is a bit of confusion here. Possibly I should have started a new post. In this particular case, I want to suppress all the generated rows for sales items (regardless of whether or not they have zero values) but I want to print all the generated rows for expense items. Essentially, I'm trying to simulate a partial expansion of expense account groups only.

If all you want is to not 'expand' the sales, first collaspe the form. Then delete .Range from column A for the Sales line, but not the row for Expenses. Thus when you expand the SmartSheet, the sales will remain summarized, but the Expenses will show each account on separate rows.

Oh! Perfect! That was easy. Thanks so much.

Richard