I'm using an Excel workbook to manage a fulfillment inventory. It contains three worksheets. Worksheet 1 includes the current inventory of all the products available for fulfillment. Worksheet 3 contains a list of the packing lists and quantity of the products (by part number) that were sent out for fulfillment this week ending with Friday's date. Spreadsheet 2 contains a column showing all the part numbers available and columns showing the count of boxes for specific part numbers that were sent for the week ending Friday's date.
What I want to do is create a macro where Excel will ask me for the Week Ending Date, reference that column in Spreadsheet 2, subtract the quantity of specific part numbers shipped from the quantity of specific part numbers on hand to show the current quantity for the week ending in Spreasdheet 1.
My reason for using a macro is that I want to be able to run it on the following Monday and provide my customer with an accounting of inventory shipped and on hand for a week ending the previous Friday. I want the macro to ask for the week ending date so I have to verify the date in order not to subtract the wrong date from inventory. It's a kind of check and balance.