Excel 2007 : can I have a master excel schedule prepop from other workbooks

[excelninjaintraining]

I am a first time poster and want to thank anyone in advance who can help me with building this system for our sales team.

I have created a "sales stats" file for 6 reps. We tried having all of them work in one shared master file but it has caused too many problems. I have built a several drop down menus in their files e.g under the "Status" they can select one of six dispositions.

completed paid standard
completed paid premium
completed unpaid standard
completed unpaid premium
scheduled call back
not interested

Now here's the question. Can I build a separate "completed" file that can look into each individual "sales stats" file and use and IF/THEN formula or something to identify only when a cell is dispositioned "completed paid standard" or "completed paid premium" and then populate all the rest of that customers fields (name, address, email, etc) into that completed file? The goal is to have the sales reps only access their files.

Are there any Excel Ninjas that can provide wisdom to this young grasshopper?

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!