Showing LIMSData Sheet Data in an Excel Template

Q: “In the large black block of space under the test results (merged cells at C35), I would like to drop text in from LIMSData!S2. Is it as simple as typing =LIMSData!S2 in the template?”

A: Rather than use the formula =LIMSData!S2, it is better to use the named ranges the template’s macro creates from the LIMS field names on row one of the LIMSData sheet. That’s a better solution since the list of fields and their order could change with a version update. In this case cell S1 will have the LIMS field name ‘Conclusions’ so you can use that name in your formula. Note that this applies to any other field name on the LIMSData sheet’s row one that you see when you export to the template. To show the contents of the cell in the first row of the Conclusions named range (i.e. cell S2), the formula is:

=INDEX(Conclusions,1)

However, the result of the above formula will show a zero if the first Conclusions cell is blank, so this formula will handle that scenario by showing an empty string:

=IF(ISBLANK(INDEX(Conclusions,1)), “”, INDEX(Conclusions,1))

You will see the #NAME? error in the cell in the template because the Conclusions name does not exist since the template’s macro only creates the named ranges in workbooks created by exporting to the template. That’s not a problem. If you would rather not see the #NAME? error in your template, use this formula:

=IF(ISERROR(INDEX(Conclusions,1)), “”, IF(ISBLANK(INDEX(Conclusions,1)), “”, INDEX(Conclusions,1)))

This formula displays an empty string if the result of INDEX(Conclusions,1) is an error. Otherwise, it displays the first Conclusions cell or an empty string if the cell is blank.