I am trying to put together a Excel spread sheet that can manage the inventory and sales. The formulas I need can take a pro minutes to do, but will take me weeks. I can email the Excel file. Here are the three basic formulas I need:
1. Sheet "Products_and_Inventory" Column "L": this number is the current inventory which is a calculation of all the purchase orders (by product) minus all the customer orders (by product) before the orders have been filled. The formula should search through all the purchase orders by product SKU (sheet "Purchase_Orders" Column "F") and identify every time a product is requested from the manufacturer and received (sheet "Purchase_Orders" Column "K") and then figure out the total quantity purchased (sheet "Purchase_Orders" Column "H"). Once we know the exact quantity of each product that we've received, the formula should search through all the customer orders by product SKU (sheet "Customer_Orders" Column "K") and identify every time a product is purchase and sent (sheet "Customer_Orders" Column "X") and then subtract the total of each product that has been sold and sent to the customers from the total of each product that has been purchased and received from the manufacturer.
2. Sheet "Products_and_Inventory" Column "N-X": this number is the amount of products sold over the period of time specified in the title. What formula should I use for these?
3. Sheet "Customer_Orders" Column "W": this cell lets me know whether the requested quantity of the requested product is in stock. The formula should search all the purchase orders (sheet "Purchase_Orders" Column "F") for the requested product SKU and calculate whether the requested quantity is in stock and then display "Yes" if the requested quantity is available and "No" is it is not.
Please let me know if you can help me.
Thanks.
1. Sheet "Products_and_Inventory" Column "L": this number is the current inventory which is a calculation of all the purchase orders (by product) minus all the customer orders (by product) before the orders have been filled. The formula should search through all the purchase orders by product SKU (sheet "Purchase_Orders" Column "F") and identify every time a product is requested from the manufacturer and received (sheet "Purchase_Orders" Column "K") and then figure out the total quantity purchased (sheet "Purchase_Orders" Column "H"). Once we know the exact quantity of each product that we've received, the formula should search through all the customer orders by product SKU (sheet "Customer_Orders" Column "K") and identify every time a product is purchase and sent (sheet "Customer_Orders" Column "X") and then subtract the total of each product that has been sold and sent to the customers from the total of each product that has been purchased and received from the manufacturer.
2. Sheet "Products_and_Inventory" Column "N-X": this number is the amount of products sold over the period of time specified in the title. What formula should I use for these?
3. Sheet "Customer_Orders" Column "W": this cell lets me know whether the requested quantity of the requested product is in stock. The formula should search all the purchase orders (sheet "Purchase_Orders" Column "F") for the requested product SKU and calculate whether the requested quantity is in stock and then display "Yes" if the requested quantity is available and "No" is it is not.
Please let me know if you can help me.
Thanks.
