I have a sheet (its the first snip at the top of the example attachment) showing two part number columns, an item column, and the amount I requested. The two part number columns can have one or two numbers depending on where the item might ship from. in the case of only one number either one of the columns can be blank.
The vendor sends a sheet with pn, item name, and qty ordered. In a day or two they'll send an identical sheet except that the last column will say shipped instead of ordered. Each of the vendors sheets occupies a tab behind my worksheet.
I need to lookup the part number of the vendor item and populate the ordered, and shipped columns on my sheet. That way I can see the progression of the numbers. Conditional formatting allows me to quickly spot shortages etc. This workbook is generated weekly. Ideally I'd like to open last weeks book, change my request numbers and paste over the other two tabs as the vendor sends the confirmation worksheets to me. Then save as a new workbook with current date. The current version has helper columns which sometimes don't work quite right. My IT department prefers that we not use macros or code so that we can hand out the sheets to other people for review or discussion without them having to know more than the absolute basics. You can't lookup on part name because they will never match. So I need to match the part number of the ordered or shipped part regardless of it's position (col A or B) in the request sheet.
Each sheet will be about 700 lines.
I attached a small example. I appreciate any ideas....
Bookmarks