Trouble when inserting row into data when referenced for combo box

[redrobyn]

I have a worksheet of data (called SA DATA) with names and addresses.
Columns ABCDEF are (A) name, (B) address, (C) city, (D) state, (E) zip, (F) country. This data source was defined as: Sales_Agents_Addresses

On additional sheets, I wanted to have the address auto fill when the name was selected from a drop down. I used the 'Combo Box' for the drop down, then used formulas - one in each cell to pull out the name, address, city, state, etc...

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

(see examples for Rich Whatever and Rob Faker in attached xls, top left corner where name and address is).

This worked fine for auto filling the address when a name was chosen from the drop down.
My issue is that each sales agent will have their own sheet, so the name needs to remain the same at the top, but if I insert new row into the Sales_Agents_Addresses data, then it changes the data on the corresponding sheets.Meaning if insert a row between Rich Whatever and Rob Faker, and put in the name and address of another sales rep, then when I go to the Rob Faker worksheet, the name and address is no longer Rob Faker, but the new entry.

Does anyone else have a better solution that would enable me to add rows and additional data into the data sheet, with out changing corresponding worksheets?
Thank you

[werfnort]

For some reason, I am unable to open your spreadsheet - but have you considered using a Vlookup?

You could set up the vlookup as follows:

Please Login or Register  to view this content.

and then continue that formula for the other variables - just changing the column number...

[redrobyn]

humm. I am not very familiar with vlookup, so I will have to see how I can make that work.
I've attached the xls again - not sure why you weren't able to open it, but hopefully it works now.

Thanks