copy cells from one worksheet to another based on condition and put in new cells in multiple rows

[breilly00]

I have a excel master spreadsheet where sheet 1 contains a list of the residents in the subdivision. For example

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]L. Name[/TD]
[TD]F. Name[/TD]
[TD]Address[/TD]
[TD]Street[/TD]
[TD]Email[/TD]
[TD]Phone[/TD]
[TD]Children[/TD]
[/TR]
[TR]
[TD]adam[/TD]
[TD]john & mary[/TD]
[TD]123[/TD]
[TD]main[/TD]
[TD]qwe@yahoo.com[/TD]
[TD]999.000.8888[/TD]
[TD]Mark[/TD]
[/TR]
[TR]
[TD]Aray[/TD]
[TD]bill & sue[/TD]
[TD]345[/TD]
[TD]first[/TD]
[TD]asd@earthlink.com[/TD]
[TD]111.222.3333[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]brown[/TD]
[TD]jim & al[/TD]
[TD]876[/TD]
[TD]elm[/TD]
[TD]abc@yahoo.com[/TD]
[TD]666.777.8888[/TD]
[TD]sue[/TD]
[/TR]
[TR]
[TD]bstell[/TD]
[TD]al & Joan[/TD]
[TD]531[/TD]
[TD]ditch[/TD]
[TD]zxc@ya.com[/TD]
[TD]777.999.0000[/TD]
[TD]all

[/TD]
[/TR]
</tbody>[/TABLE]











Now I want to be able to produce a phone directory (see below) without having the board members to do any thing other than update the master spreadsheet (sheet1). So if they..
- Change a row with a new resident because the homeowner has moved out the telephone directory would automatically be updated. (new person should show up in new alphabetical order)
- Change the phone number, or any other data, the directory is also updated.
- the directory can be sheet2 or another workbook in the same workbook.
- No new houses will be build, so the number of rows will not change.
- The layout of the mastyer sheet is not fixed in stone. It was done this way to provide ease of maintenance


What the directory should look like . This is not set in stone either. If the format can be changed to make the formulas or macro easier so be it. It should fit on an 81/2 by 11 paper, be hard copy and portrait vrs. landscape is prefered.

[TABLE="width: 500"]
<tbody>[TR]
[TD] A[/TD]
[TD] N[/TD]
[/TR]
[TR]
[TD]Adam, John and Mary[/TD]
[TD]Etc. etc. etc[/TD]
[/TR]
[TR]
[TD] 123 main .................... 990.000.8888[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aray, Bill & Sue[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 345 first...................... 111.222.3333[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Brown, Jim & Al[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] 876 elm.................... 666.777.8888[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bstel, Al & Joan............... 777.999.0000 [/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Our residents have last names with all letters except E, I, Q, X. We could hard code or do a template for the entire alphabet and and just leave blank the names under these letters. Also we are not tied to the 2 lines for each person (one board member thought it looked nice).

I am trying to get the least maintenance into this so future board members can just maintain the master list and the directory is created on save, on event, on button. (does not matter). I do not know how to get the data from some columns in a single row into a formatted directory and under the correct letter of the last name.

Can any one give me a example of what i should do????