I think I understand the goal, and I think you’re attacking it sideways. Here’s an alternative approach that should make you happy.
How many distinct possibilities are there? You listed “inbound oil tankers, explosive loadout, minor hazmat spill, ship departing with a down RADAR”, which are 4 examples of possible events.
Ignoring multiple examples of the same event (ie 2 tankers), how many distinct possibliites do you intend to have in your list? Fewer than 50 will work better, at least for the simpler of the two versions I’m going to propose.
I suggest you use Excel, not Word. The reason why will become evident as we go along.
Put the list of possible events in column B with one event per row. Use some logical order, alphabetical or by category of risk, whatever makes sense to the operator. Widen the column as needed so it holds the text easily.
Column A is blank, and the operator will put the quantity (i.e. 1, 3, 5, 2, etc) in each row where one or more of those risks exists. They’ll leave the A cells in the other rows blank. A cells are the only cells they ever type into.
Column C contains whatever formula is required to evaluate the risk for that row’s event. It may be as simple as “multiply the qty in A by some fixed factor” for minor events, or it may have more complexity, such as multiply by fixed factor #1 for the first 3 occurrences, then multiply by larger fixed factor #2 for any additional occurrences, to reflect that beyond 3 events you’ll need to borrow assets if stuff goes wrong. etc. etc.
For maintainability I’d put those factors in other columns out to the right offscreen, perhaps starting at column AA, rather than building them directly into the formulas. Another technique is to put them only a couple of columns to the right of the meat of your table, but then hide the columns so they’re invisible. That reduces visual clutter for the user and ensures folks don’t diddle with them.
At the bottom you can simply total all the column Cs, or if there’s more interaction between threats so a simple total isn’t spohisticated enough, you could put formulas that merge the results of several C cells into a column D cell. And so on. Multiple D cells could be merged into an E cell, whatever it takes to get the degree of risk interaction you need.
Format all the column C, D, E, etc cells to display blank if zero. That avoids distractions in the final report.
Finally, carry the total, however derived, to some clearly labeled bottom line cell beneath the event table.
Last of all, set the print area to encompass vertically all the rows and the total; and horizontally at least columns A-B, or more as you see fit.
After it’s done, lock all the cells except column A of the event rows so fumble-fingered operators can’t break your formulas.
To use it they just open a blank copy, fill in column A and hit “print”. Maybe you want to save each day’s (or shift’s) version of the file, or maybe you just want to print it and discard the xls.
Thats Version One.
I agree that’s not ideal since lots of rows are unneeded, but the recipient of the report can easily scan down column A and pick out the few items that aren’t blank. If there are only a few, the person preparing the report could highlight those few rows with a yellow marker easily enough. Certainly the fewer total possible events in your list, the better this works.
Now for Version Two.
There are ways to filter the printing so ONLY the cells with non-blank column As are displayed/printed. And it’s pretty easy.
Here’s how:
Put a header row just above the first row of events, with text identifying the columns. Select (highlight) Column A from the header row down thru the last event row. While that’s selected, choose Data -> Filter -> Autofilter from the main menu. You’ll see the text in the header row is still displayed, but now it looks like a dropdown box too.
After the operator finishes filling in the form, click the dropdown triangle for column A. The bottom choice says “(NonBlanks)”. Select that choice and all the other unneeded rows disappear. Magic.
To return to normal, select the top dropdown choice in column A which is “(All)”.
Viola.
Finally, assuming you use the filter technique, then my original concern about having less than 50 events is moot. You can have 400 or 4,000 potential event rows as long as you filter the printout.
You can also insert dividers, subtotals, etc., between sections of the report, and as long as the divider rows have something (say “===”) in Column A, they’ll always be displayed in the filtered report.
Lemme know how it works out. If you’re fuzzy on any of the specific techniques I mentioned, ask again.
You’re welcome.