Table of contents

Suppressing the Heading on the Notes column

Last modified
Table of contents

An accounts page often contains a Notes column which contains note references. However if there are no note numbers to be printed, users often wish the column heading "Notes" to be suppressed.

The obvious way to do this is to perform a test directly on the note numbers themselves, e.g.

=AP("If ((note,note01)=f0) And ((note,note02)=f0) And ((note,note03)=f0) Then `` Else `Notes` Endif")

However Accounts Production does not allow tests on note numbers in the same way that it does not allow tests on page numbers. See Suppressing Rows on the Contents page.

A more complicated workaround is required. Suppose:

  • The notes are on rows 11 to 13. Row 11 refers to note 1; row 12 refers to note 2 and row 13 refers to note 3.
  • There is Data Regulate on each of these 3 rows. This leaves cell A11, A12 and A13 empty as formulae are not required to suppress the rows.
  • We wish to print the heading "Notes" if any of rows 11 to 13 will print.

The solution is as follows:

  • In cell A11 add a formula that gives the result 1 if the row is going to print, and 0 otherwise. The formula might be:

=IF(COUNTIF(D11:G11,"<>0")>0,1,0)

This is a pure Excel formula that counts how many of cells D11 to G11 are nonzero and shows 1 if any of them are nonzero, i.e. if the row is going to print.

  • Rows 12 and 13 have similar conditions to print 1 in A12 and A13 if their rows are going to print. Since column A is suppressed, these values do not appear in the accounts.
  • The formula to print the column heading Notes is then:

=IF(SUM(A11:A13)=0,"","Notes")

In practice, the solution may need to be varied slightly if column A contains a row suppression formula. An example is to modify a formula that shows "print" if a row will print, to show the number 1 instead, e.g. change

=IF(AP("#cd14")<>0,AP("RS"),`print`)

to:

=IF(AP("#cd14")<>0,AP("RS"),1)

Page statistics
162 view(s) and 6 edit(s)
Social share
Share this page?

Tags

CCH CGT & Dividend Scheduling

Comments

Attachments

 

 | Cookie Policy | Copyright | Privacy Policy Terms of Use | Contact Us |