google sheets create lookup sheet for imported excel files

Project name: google sheets create lookup sheet for imported excel files Important note **We don't want this in excel, work must be done in google sheet** This is the process we need to follow ** please note points 1,2 are just for your understanding of how it works in our system** > 1. PDF is converted to excel file on iMac 2. excel file is uploaded to google sheet - we use apple excel This is the part you (freelancer) needs to create in google sheets only > 3. google sheet need to read the excel file we import 4. google sheet needs to read the product ID ie SBALB5 (I can provide a list of all products IDS if needed for a lookup sheet) SBALB5 in stock or out of stock (we have a seperate google sheet to show in stock and out of stock product ID) - if in stock leave > if out of stock, drop down box shows suggested product substitutes from the other colours available in product style ie red out of stock, dropdown shows blue, black, grey > select product ID from drop box to populate / replace imported out of stock product id 5. Need to add total sales and how many pcs sold > records totals on other google sheet 6. New sheet to record which product ID have sold > everytime a new excel sheet is imported it records the product ids after above process has occurred on a new google sheet > for example 55pcs of SBALB5 since date 1st JAN 2019 > Need products to be in order example BS1, BS2, BS2, RE1, XT2, XT9 7. Once all above is finalised in the process, it will then save in a folder on google drive and we will be able to search for customer order by name, order number > This can be saved on one page or seperate sheets in google saved file I have attached current out of stock google sheet row example - this is for point 5 above - Green box is for total sales for the day - pink box is for total products sold for the day Second file attached showing our in stock and out of stock - Green cell in stock - Red cell out of stock