ACCA Manual J Abridged Edition Speed Sheet compatibility

shadow1 · December 11, 2023, 4:32pm

Hello, a report output (of a given spreadsheet) that is required by public sector in United States depends on proprietary software Microsoft Excel and fails to be generated with LibreOffice. This is an incompatibility in LibreOffice, but how and what exactly is the problem that it cannot correctly run the report? The effect is that freedoms are limited and Microsoft Excel (or any alternative vendors’ industry-approved proprietary software) is required to generate this report.

Background info on what this is:
When submitting building plans to regional government in United States there is often a required report “Manual Joule” describing heat loads that can be generated by a spreadsheet of the ACCA organization. Web search “acca manual j speed sheet” to find the downloadable resource from ACCA website.(1*) The report has to do with heat load in built structures and is dependent on proprietary information in a printed reference book “Manual J”; On read of the Manual J reference book it tells the reader to use any ACCA-approved software to generate the report. Among the many ACCA-approved proprietary software packages from private sector companies there is also allowed to use the ACCA-provided Manual J Abridged Edition Speed Sheet spreadsheet with Microsoft Excel. Great, right? We could use LibreOffice Calc and preserve our freedoms? No, unfortunately, LibreOffice Calc is not compatible with Excel for this document and the report fails.

I have just completed this process myself as owner-builder for a home, and after many hours of confusion I did realize that the report generation is broken with LibreOffice Calc.

My question and for a discussion: Where is the formula or code that does the work? What exactly is the incompatible feature or method?

I can provide a more complete example with data inputs used on my home permitting process, or refer to some walk-through of what data is suitable for entry to generate a report in Excel. Initially I will just refer to the original document as that is the most trustworthy (and not me asking others to open an interactive document).

Links:
1*: “MJ8ae 1.28 27DEC16.xls” Downloads - Public Files - ACCA

JohnSUN · December 11, 2023, 5:01pm

Welcome!
If you believe the information that can be obtained about the file using the File - Properties - Statistics tab, then in this book, in addition to the visible 13 sheets, there are 63 more that the developers hid from users.

One of the sheets is called Cities. There in cell N74 there is the formula =HLOOKUP($M$1;$N$1:$BL$74;L74). As you can see, cell N74 itself falls in the range $N$1:$BL$74, which leads to error 522, “Circular reference”

If you can get to this cell and correct the formula (or come up with another way to fix this problem), then most likely most of the other errors will disappear on their own.

shadow1 · December 11, 2023, 5:17pm

In this situation it is not allowed to modify the spreadsheet code of the provided document. When opened with Microsoft Excel there is a warning about this circular reference and the choice of OK or CANCEL ; choosing CANCEL preserves the spreadsheet as-is and enables some compatibility mode within Excel, and after data entry is done the report generates normally. LibreOffice Calc fails to generate the same report.

erAck · December 11, 2023, 5:58pm

And if in Excel you hit OK instead of Cancel then it fails as well? Anyway, get the author of that document to fix things.

JohnSUN · December 12, 2023, 8:46am

This would be the right approach, if not for one significant “But”…

Kenny Watson created this table over 20 years ago and last modified it seven years ago. He is no longer the same 30-year veteran in the HVAC industry; he is a respectable person who is unlikely to want to return to his old project. At the very least, this will require a very convincing motivation. (Perhaps I’m wrong, I’m just judging by myself - if I remember that I already solved some problem a quarter of a century ago, then I’ll just try to find that solution in the archive, but it’s unlikely that I’ll want to remember how and what was done)

Yes, it would probably be clearer. We are not HVAC specialists, do not know the terminology and do not know what data should be entered into the table cells and what result should be obtained. We could probably help you trace the chain of errors from formula to formula so that you can correct something and get the desired result. But I really don’t want to change the original workbook. The fact that we willingly give away our intellectual property for free does not mean that we neglect other people’s intellectual property rights - therefore, we will not modify anything in his creation without the express permission of the author.