I have a shop where 40 vendors sell their products from rented space in my shop. I ring up all vendor sales to all customers. I prepare a weekly listing (worksheet 2) of all sales by all vendors, in the order sold (vendor #, date, item description, sales amount). I prepared a worksheet (worksheet 1) with each vendor's information (vendor name, vendor #, rent amount, commission rate, etc), My third worksheet will be a Weekly Sales Summary for each vendor (vendor name, #, each sale by date, description, amount) with a subtotal of all that vendor's sales, then minus rent and calculated commission, and then a total net sales amount. I will then print worksheet 3 for each vendor.

I would like to create worksheet 3 as a template, so that a simple entry of a specific vendor number into worksheet 3 will pull that vendor's data from worksheet 1 and worksheet 2 and populate worksheet 3. And then worksheet 3 will calculate the sub and net totals.

My problem is how do I select only the specific vendor's sales for the week from worksheet 2 and then copy or pull all of that data (date, description, sale amount) into worksheet 3 without manually sorting and copying for each vendor? And same problem for data from worksheet 1 into worksheet 3.

When I describe this, it sounds like a simple process, but I have not found the formula or combination of formulas that will do this. So all thoughts and suggestions will be greatly appreciated.