Hi, (my contact info is at the bottom)
Im currently working on a fairly complex spread sheet to track letters that need to be sent out and I'm trying to figure out how to get excel to automatically transfer the required information into a preformated word document. Along with some additional stuff. There are several conditions that are required. The spread sheet contains multiple worksheets that are identical in setup, but are seperated based on who is responsible for them.

currently the spread sheet is written as follows:
a; b; c; d; e; f; g; h; i; j; k; l; m; n; o

Notice Flag; MBR name; MBR Address; Doc #; claim #; Claim creation date; Doc balance; Doc age; Letter 1 due date; Date L1 sent; L2 due date; Date L2sent; L3 due date; Date L3 sent; Date resolved;

The "doc #" is essentially the members account number, and the "claim #" is how many claims the member has (claim # can only be 1 or 2). "Doc age" is claim creation date minus today.

Basically the spreadsheet is designed as a roster that tracks members that have an outstanding balance and notifies us via the "notice flag" that a letter is due based on pre-defined time lines. The first letter needs to be sent out 40 days from the "claim creation date" if their claim # is 1 and 30 days from the "claim creation date" if their claim # is 2. L2 needs to be sent 15 days from the first letter is sent (actual mail out date that is manually entered) and L3 needs to be sent 15 days from the second letter.

The first thing that I am trying to figure out is how to get the "notice flag" to display "letter 1 due", "letter 2 due", or "letter 3 due" based on the above criteria as long as the letter has not already been sent (e.g. the "date L* sent" is filled in with a date) and as long as it has not been resolved (e.g.)

The second thing that I am trying to do is to create some sort of macro button that I'm calling " GENERATE LETTERS NOW" automatically fill in the Name, address, doc #, Doc creation date, and balance due into a preformated letter. And then automatically repeat the process through a loop until all of the letters have been sent. Also, L1, L2, and L3 are all different letters so I need it to transfer the info to the corresponding letter that needs to be sent out. (if possible I would love to be able to have it automatically print each letter also)

I'm not completly fluent with programming in excel but I do know how to write basic formulas (e.g. if, and, or, etc.) so please explain in non-tech. I'm not expecting anyone to actually create a whole design for me, but any help would be great. Also, where can I go to learn how to do all of this stuff on my own.

Im not sure if the forum will automatically notiy me of any response to this thread... if not feel free to email me: TimBaileysBox+Excel@gmail.com.. Please be sure to include "+Excel" in the name otherwise it will auto delete.