Is it possible to copy and reference data within a range of value to a new sheet based on the information it contains?

[marciaivey]

I feel very competent in most functionality within Excel, but I do not know if there is an answer to my current problem that can be shortened by a formula or shortcut trick. Let me briefly describe the intention and functionality of the desired outcome below.

Information is copied and pasted into a master database with data from monthly loan sales by salesperson. This master database is then disaggregated into a separate sheet for each salesperson as a type of report for them to use in their monthly evaluation. The current method for creating this is to filter or sort the master database and copy/paste the data from the master to each individual sheet located within the same file based on the salesperson's name.

My main question is this:
Is there a way through a reference or "if" formula to tell a particular sheet to fill in a new row of data IF it is entered onto the master database sheet?

For example: if I upload the new month's information to the master database and it says Scott sold 3 different loans, could the separate sheet with only Scott's information be automatically fed that information as it is initially put in to the master database? If not automatically fed, is there a formula that can be used to simplify the process of copy/paste within multiple sales sheets?

I tried browsing through videos that I thought would help with this, and happened upon many other helpful tips, but I didn't quite find an answer to this situation.

Thanks for any help or suggestions you can provide-

Marcia Ivey

 

[VoG]

Try Excel Explosion. You will need to delete each rep's sheet each time before you use it.

Link http://www.datapigtechnologies.com/freeware.htm

 

[marciaivey]

Thanks for the suggestion, but I was hoping to set up a template where each month the employee could just open a blank version of the template and copy once into it onto the master database without having to re-copy for each employee or re-enter criteria. Would that goal be reached using this tool?

 

[VoG]

You can try my code here http://www.mrexcel.com/forum/showthread.php?t=396069

You (or the users) will still need to delete the child sheets before running but in this case an error won't be generated if a sheet exists but you'll get Sheet21 or similar.