Automated Final Report Footnotes in<br>Excel

Q: Is there some way you could write a macro when doing the COAs to put an automatic footer on the report if in the results there is an *? In our old system, * stood for Estimated plate count and when a report had the * it would automatically put the footer in.

A: An Excel template’s AfterTransferFromLIMS macro could be updated to scan the report’s data for an asterisk and append a footnote if detected. However, that requires modifying VBA code to change the footnote and is more difficult to transfer from one template to another.

A better solution is to use an Excel formula in a cell to append the footnote. This solution is simple if your report was created from one of MSC-LIMS’ example final report templates.

To decide where to add the formula, first determine whether your template uses a SignatureBlock named range, which is automatically copied to the first worksheet following the report’s data. Look for the SignatureBlock named range in your template in the Name box immediately above the intersection of row and column labels. If it exists, select the SignatureBlock name to see the cells that comprise the range, which are normally on the Settings worksheet (see above).

Select a cell within the range and use Insert | Rows to automatically expand the SignatureBlock range for the new footnote. If your template does not have a SignatureBlock named range but includes a signature line or disclaimer on the report worksheet, add the footnote formula above these cells. Use the following formula to display a footnote:

=IF(SUMPRODUCT(ISNUMBER(SEARCH(“~*”,Anal ysisResultFormatted))+0) > 0, “* Estimated plate count”, “”)

The formula above looks for an asterisk anywhere within the AnalysisResultFormatted named range. The AnalysisResultFormatted range is automatically created by the template’s AfterTransferFromLIMS macro and contains the formatted result for each analysis exported. If an asterisk exists it displays the given text string, otherwise it lists an empty string. Note that an asterisk and a question mark are wildcard characters for the SEARCH function. To find either of these characters with the SEARCH function they must be preceded by the tilde (~).

You can use multiple formulas to add other footnotes for other result qualifiers. Add this formula to your templates and make your final reports smarter adding footnotes only when the report’s data require the note.