Have you considered using MegaMekLab for warship construction instead of making your own spreadsheet? The only shortcoming I've encountered with it is ammo assignment, but I have a text-editing workaround for that.
My fun part is that when I allocated Capital weapons to a bay MML will create a "Capital (Weapon) Bay" and put the Capital weapon as part of it. I then select to remove all weapons (to put them back in the right-hand listing), and MML then moves the weapons back and also moves the "Capital (Weapon) Bay" as well.
How to test it:
Open up MML
Create a 100 kton Warship, expanding the screen so there are no scroll bars when in the "Structure/Armor" tab
Add the necessary heat sinks, Officers, and crew Quarters to Validate the unit.
Go to the 'Equipment' tab -> Capital Weapons -> Add 8 HNPPC
Go to 'Assign Criticals' tab -> Drag and Drop a single HNPPC into the Forward left bay (creating a "Capital PPC Bay")
Click on the "Reset" button in the bottom right
Result:
The "Unallocatd Equipment" list on the right-hand side will list all eight of the HNPPC, along with a piece of equipment called "Capital PPC Bay" that masses zero tons.
Recommendation:
MML should not add the "Capital (Weapon) Bay" to the 'Unallocated Equipment' list
I am using Version: 0.48.0
For creating your own spreadsheet, it allows the designer to learn the spreadsheet software, and also allows for extra flexibility for House Rule ideas
(i.e. a Battle station that has station-keeping drives but SI higher than 1, though cannot jump)