First Post - Formula help greatly needed

Copper Contributor

Hello Peeps, 

Hope you guys are well, I'm very very new when it comes to Excel, so for some this will be the most basic thing. So by profession I am a doctor, but before Med school I used to sell little stuff on eBay, and Amazon this is something I still enjoy doing despite the lack of real profits. 

To really make the help required easily comprehendible I have made a template file of all the stuff in simple format. Would love to hear from you guys how to improve this. 

The three formulas I am struggling with are explained below.

Q1 - I have three suppliers for identical products, now within these suppliers the price varies as its subject to the USD rate. So I need in column E, G & I the most recent price based on invoices. I don't know how to write this formula but understand that this will be based on a condition of date and supplier. 

          Date:  16/03/2023 Date:  14/03/2023 Date:  08/03/2023 Date:  01/03/2023 Date:  12/02/2023 Date:  10/02/2023 Date:  01/02/2023
          Invoice #:  5001 Invoice #:  A212 Invoice #:  A208 Invoice #:  XYZ721 Invoice #:  4985 Invoice #:  4722 Invoice #:  XYZ718
   SupplierASupplierB Price SupplierC Price Supplier C Supplier B Supplier B Supplier A Supplier C Supplier C Supplier A
Item Title SKUPriceSupplier A SKUA Price Supplier B SKUB Price Supplier C SKUC Price SKUQTYPrice SKUQTYPrice SKUQTYPrice SKUQTYPrice SKUQTYPrice SKUQTYPrice SKUQTYPrice
Banana FB0A101 FB54 X0A056  X0A0564£2.00 FB542£2.00 FB546£2.00 FB546£1.00 X0A0564£2.00 X0A0560£1.95 FB546£1.20
Apple FA0A102 FB55 X0A057  X0A0574£4.00 FB553£4.00 FB556£3.50 FB552£1.00 X0A0574£4.00 X0A0570£3.85 FB556£1.00
Orange FO0A103 FB56 X0A058  X0A0584£3.50 FB560£3.50 FB566£3.00 FB566£1.00 X0A0581£3.50 X0A0582£3.40 FB566£0.99

Q2 - In sheet named 'Reorder Sheet' I need to use data from sheet 'Restock Data' to generate reordering quantity. So this is a COUNT formula but because first three items are single units and the rest are bundles of the said three different units (see sheet name 'listings'), I am unsure of how to make it count from the bundles. 

Item Title SKUUnits RequiredSupplier A SKUA QTYSupplier B SKUB QTYSupplier C SKUC QTY
Banana FB A1010FB540X0A0560
Apple FA A1020FB550X0A0570
Orange FO A1030FB560X0A0580




Item Title ASIN SKU Number of UnitsPriceUnit 1 SKU Unit 1 PriceUnit 2 SKU Unit 2 PriceUnit 3 SKU Unit 3 Price
Banana B0001FB1£5.00FB£0.00    
Apple B0002FA1£5.00FA£0.00    
Orange B0003FO1£5.00FO£0.00    
Bundle 1B0004SET-BA2£9.00FB£0.00FA£0.00  
Bundle 2B0005SET-BO2£8.50FB£0.00FO£0.00  
Bundle 3B0006SET-AO2£9.00FA£0.00FO£0.00  
Bundle 4B0007SET-BAO3£12.00FB£0.00FA£0.00FO£0.00

Q3 - This I don't know anything to be honest. I hope I can explain properly. So basically I need the values in cost of goods column in Sales - May 23 to be based on the invoices, so once a quantity is used up from the oldest invoice (in catalogue sheet) I need the formula to jump to the next invoice and use the respective price. I gave up on this and just thought using an average will suffice but that makes it more confusing. 

788782B00061£10.01£10.01£2.50 £0.20£2.50£7.51£1.86
788783B00031£5.22£5.22£2.00 £0.20£2.00£3.22£0.97
788784B00041£9.85£9.85£2.50 £0.20£2.50£7.35£1.83
788785B00051£11.00£11.00£2.50 £0.20£2.50£8.50£2.05
788786B00051£11.02£11.02£2.50 £0.20£2.50£8.52£2.05
788787B00061£9.89£9.89£2.50 £0.20£2.50£7.39£1.84
788788B00071£11.95£11.95£3.00 £0.25£3.00£8.95£2.22
788789B00011£4.85£4.85£2.00 £0.20£2.00£2.85£0.90
788790B00071£12.44£12.44£3.00 £0.25£3.00£9.44£2.31
788791B00041£10.20£10.20£2.50 £0.20£2.50£7.70£1.90
788792B00071£13.15£13.15£3.00 £0.25£3.00£10.15£2.45
788793B00071£13.11£13.11£3.00 £0.20£3.00£10.11£2.44
788794B00013£6.07£18.21£2.00 £0.20£2.00£16.21£3.39
788795B00042£10.29£20.58£2.50 £0.20£2.50£18.08£3.83
788796B00071£11.74£11.74£3.00 £0.25£3.00£8.74£2.18
788797B00061£9.85£9.85£2.50 £0.20£2.50£7.35£1.83
788798B00071£11.70£11.70£3.00 £0.25£3.00£8.70£2.18
788799B00043£9.52£28.56£2.50 £0.20£2.50£26.06£5.31
788800B00011£4.99£4.99£2.00 £0.20£2.00£2.99£0.93
788801B00061£11.02£11.02£2.50 £0.20£2.50£8.52£2.05
788802B00071£13.99£13.99£3.00 £0.25£3.00£10.99£2.60
788803B00019£5.00£45.00£2.00 £0.20£2.00£43.00£8.37
788804B00012£5.09£10.18£2.00 £0.20£2.00£8.18£1.89
788805B00012£5.05£10.10£2.00 £0.20£2.00£8.10£1.88
788806B00071£12.28£12.28£3.00 £0.25£3.00£9.28£2.28

Any help will be highly appreciated, the file attached is obviously not the real data, this is because I wish to understand how the formulas work in a template so I could recreate it - for me this more of a learning thing. I tried to use Fiverr and the people are on there were prepared to do it for very reasonable prices, but don't want to teach me how the formula is written which I found weird to say the very least. 

1 Reply

why can i not attach the full file here?