MS Word, maybe Excel help request

Factual Questions
1

I have an MS Word doc, that has form fields (with dropdowns). These form fields allow you to choose one item from a list, then automatically continue to the next form field dropdown box. What I need to do, is change it so that I can choose more than one item from the list, per field. IOW, when I choose an item from the dropdown box, I’d like for the form field to automatically default to a new form field in the same category to possibly make multiple choices in one category. Does that make sense? Is it possible?

Also, I’d like to assign each choice within a category a value, and have that value totalled in another field. Do I need to abandon Word and move to Excel at this point?

FWIW, I have Office 2002, and I’m (obviously) a novice in these areas.

Any thoughts?

2

A dropdown box is only good for one choice. You will need to create a List Box where one can choose multiple selections with the Ctrl key.

And that is all I can help you with…

-Tcat

3

That’s a tough problem. Word and/or Excel can be programmed to do damn near anything, but it isn’t something that is point-and-click simple enough to be described here.

Right now, sticking with dropdowns I’d say we’re at a dead end without someone being willing to write a bunch of word/excel programming for you.

I suggest you back up a level and tell us more about what large-scale goal you’re trying to achieve. What is the purpose of the form, what goes in those various dropdowns, how many are there, how do they interact, etc? Perhaps we can come up with an alternate way to achieve substantially the same result.

Good Luck.

4

OK - this is essentially a daily briefing sheet for operations within a port. I need to include a risk analysis, which as it is now, is a whole separate .xls doc, and time consuming. So, I envision this:

A daily document, starting with single choice items. (Already done)

An area where an individual can choose from a list, what is going on that day - i.e. 3 inbound oil tankers, 1 explosive loadout, 1 minor hazmat spill, 1 ship departing with a down RADAR, etc. Plus, perhaps a separate list of choices for other aspects, such as weather, etc. So, there may be one or two items today, 15 tomorrow. Each one of these choices would be assigned a point value, and tallied automatically to provide a quick summary of risk level (basically a red, yellow, green type system perhaps).

So, for example, I would like a drop down box(or whatever works), that has many choices of what may happen in the port, and be able to choose any number between 1 of them and all of them, and have the corresponding point values provide an automatic risk summary, based on a yet to be determined formula.
:eek:

All this, times 3 (for 3 different port areas), user friendly, simple and idiot proof.
:eek: :eek:

Essentially, the end goal is a simple numerical value representing risk or activity - so I’m trying to keep this as simple as possible. Sound do-able for an amateur?

5

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.

6

LSLGuy, thank you very much for that - I’ll give a whirl and see how it goes.