My situation is this:
I have an Excel Spreadshet with two sheets...the first one is populated with invoices and the contents of this purchases (Items codes) Sheet 2 have my inventory separated in ready Items and Raw Items (need finishing before delivery)
The sheet goes like this:
Sheet1: (Invoice Details)
Column A: Invoice Number
Column B: Date of Purchase
Column C: Customer Name
Column D: Item Code
Column E: Qty Purchased.
Column F: (Here come´s the formula I need)
Sheet2: (Items information)
Column A: Item Code
Column B: TOTAL Item Inventory (ready + raw items)
Column D: Items Ready to load
Column E: Raw Items (Need Finishing before be loaded)
What I need:
In column F on Sheet 1 I need a Macro or a formula to display the STATUS situation on each Invoice product based on the ITEMS RAWS, ITEMS READY or No available ITEMS....and the Criteria to asign the product ready comes from the Date of purchase.
If Posible, The Macro/Formula should display in column F on sheet 1 the action to be taken i.e:
"To Assemble" (When the total of purchased items is bigger than Ready Items but minor than Ready + Raws
"To Order" (When the total of purchased is bigger than Ready and Raws available.
The solution is probably comes from a combination of Counts, If and something else...
I appreciate very much any help or directions.