drop-down list vs if-then vs ??

[thetis]

I am looking for help in creating some kind of form and I don't even know what questions to ask. It needs to have drop-down lists that will eventually create a narrative. I have recently (today) figured out how to create drop-down lists and basic if-then functions, and am now trying to figure out which would be best to use, or a combination, or something else entirely. Maybe even another program? I don't know, but I have been told to use Excel. I'm glad to have a chance to learn how to do this sort of thing, but my main stumbling block is that I just don't know what's possible or what the best options are, so that I can find the appropriate tutorials and learn how to do it. Here's a rough version of what I need to do:

1st drop down: Title (Mr, Ms, Miss etc)

2nd drop down: list of names

3rd drop down: gender - and then it gets complicated, once the gender is selected, everything after this will have the correct pronoun.

4th drop down: Successful? yes or no - and then it gets more complicated....

If no, there are two reasons: the person withdrew voluntarily, or the person was removed.

After selecting one or the other, I would then need to select one of various reasons why they withdrew or were removed.

If yes, there needs to be a selection for how successful they were (average, good, exceptional etc), options for why they were successful, and some kind of narrative about their exam score being X%.

The end result needs to be a paragraph of text, something like this:

"Mr. Smith successfully completed all performance objectives of the Excel course and is qualified as an Excel Guru. Academically, he achieved an overall course mark of 85.2%. He demonstrated above average writing skills and proved to be a highly motivated and outstanding programmer. Mr. Smith displayed a respectable level of physical fitness and his enthusiasm greatly contributed to the success of his team. He is highly recommended for further Pivot training and is encouraged to attempt the Excel-Guru-Next-Level workshop."

Thank you very much for any tips or ideas you can provide on how to tackle this!

[#2]

@thetis 

Based on what you've described, my own recommendation would be to use Excel as your data source, but Word (specifically Word's Mail Merge capability) as the place were output takes place. Word has the ability to do "conditional" merges--so it could handle the gender appropriate pronoun, for example And if you have in Excel a series of rows consisting of Title, Name, Gender, CourseName, Grade.... Mail Merge could run off a whole series of letters, adapting to the conditions per your instructions.

It'll be tricky learning it, but it's VERY do-able in that kind of combination. Do some searches in Word Help (or here in the Word techcommunity) or on Google....searching for "conditional mail merge" or terms like that.

FWIW, you might find it challenging to try to write one process that handles ALL conditions. It's possible; it's cause for celebration if/when you succeed. But you might preserve your sanity more effectively by writing one mail merge letter that handles those who successfully complete; another for those who don't. Just a thought, after re-reading the potential complexities here.

The drop-down stuff is relatively easy (sounds like you've already gotten that mastered). The conditionals for the output are not all that hard, but make it easier for yourself by being willing to have two (or maybe even three?) letters for the fundamental categories. Then you can be more creative WITHIN, say, the successful. One size doesn't necessarily fit all.

[#3]

@mathetesThank you for your reply! I've looked into the mail merge function and agree it looks like something I could create, but the problem is that I will not be the one actually using this to create the final reports - rather it will be about a dozen different people creating reports for their respective classes, and I'm not sure that it would be feasible for them all to go through this process even after I set up a template. The best possible scenario is where there is only one program for them to deal with. One of the instructors has told me that in a previous job, he had an excel file with all the drop-down choices to select, and the text would appear in a larger cell below as he went through all the drop-downs. Is there a way to have something like the mail merge program be embedded in the excel document?

Thank you!

[#4]

@thetis 

Oh, my. That IS a challenge. My own sense, however, is that getting something like that to work solely in Excel would actually be more difficult for that diverse group of instructors--especially if they're older folks (I say that as a 78 year old; I'm well aware that most of my contemporaries aren't as comfortable with computers as am I)--than would something using Word. More people are more familiar with Word, in general, albeit not with mail merge....

And to produce a series of letters for different students (or whatever), in Excel, would require a VBA  or macro routine, whereas in Word mail merge that's exactly what it does with whatever population has been selected.

But I'll defer to others here who may have had experience producing the kind of letter you're describing from Excel. I know it's got remarkable capabilities, and know also that I've not tapped into all of them.