Hello All,

I'll be honest - I could be posting this in the wrong sub-forum, but I am going to explain what I am trying to achieve and why, and perhaps some of you FAR more knowledgeable people can steer me onto the path!

I'm an instructor, and at the completion of a course we produce course reports. The report template is a word document which has several fields, including name, course number, etc. The bulk of the report is a narrative. The narratives specify certain things, such as so and so completed this, achieved this, etc. Not all narratives are the same, but at the same time there is really nothing unique about them. Further, we have _LARGE_ numbers of people on these courses.

So clearly... going through EACH document for EACH person and changing them all individually is a major PITA. No fun. So I was steered onto Mail Merge and WOW! That made it a lot easier. So I've told you a story, you're probably curious about the problem...

I use the mail merge to fill out the obvious data fields. The issue is the narrative. Currently, in an Excel sheet, I have fields that you put a # in (1, 2, 3, etc) which in the Word document has an IF function that then reads the # and gives the correct statement. Only issue is, it's messy. VERY messy in the word document, PLUS, just having a # in Excel means A) if they don't put anything or put an incorrect # it can screw it up and B) They can't really see what they are putting in while editing the Excel DB.

Then I learned how to create a Drop Down List in Excel. HUGE difference, MUCH easier. And I learned how to put the variables in another sheet, so it is easy to edit. So again... the problem... The narratives use words such as HE, SHE, HIS, and HER. In the DB, each person has all their info along a ROW. One of the fields is GENDER. Is it possible in the variables to include a function that checks the gender to produce the correct word?

Example.

The first row might have cells including NAME, AGE, GENDER, etc.

Then further down after all the data rows, I put my drop down lists.



One might be PREPARED.

On another sheet, I have two cells in which the narratives are written. One might need be "He arrived prepared for all tasks, bringing all required equipment." Is there a way such that when I go to select it from the drop function, it can then pull the GENDER from the same row it's in and put it through an IF function to produce either "He" or "She" (or really whatever I need)?

Wow... this is LOOOONG. But hopefully someone has an idea for me. This would then allow me to easily pull data (ie. the sentences) into the Word document for a mail merge. A heck of a lot easier than the exceptionally dirty and hard to correct IF functions I have all nested into each other using only #'s from the excel database.

Thanks!

Guessing maybe no one does... maybe I can try another program like access or something.

Okay, lets see if I can expand and change this question a bit....

I figured out how to mix text and a formula within a cell. Awesome. I also figured that since the column with GENDER is the same, and only the row changes I was able to use row() to define the cell I need.

New problem... And I am sure there is a solution, just trying to locate it. This might turn into more of a working log than anything

My cell with the variables for my drop down list is in sheet 2. My data in sheet 1. Sheet 1 is also where the drop down list is. When I drop down and select my variable, it evaluates it, but gives me the row of the original variable in sheet 2. Is there a way to have the function re-evaluated once it's selected on sheet 1? Or perhaps a function that pulls from sheet 1? Thoughts?