summarise one sheet and have results sent to a second worksheet

CSM24

New Member
Joined
May 13, 2019
Messages
6
Hi,
Could you please offer some advice on following?
Our current accounts package sage 50, cannot calculate any on time delivery performance natively so I have to out reports to excel I have two worksheets in the same work book for monitoring supplier performance,


Sheet 1 “Supplier Register” pulls data down at account level. And incorporates account data to use as a supplier risk database.
Sheet2 “Sup OTD” pulls itemised delivery data for each supplier/order. Delivery performance is calculated using the difference between delivery due date, and date received in the either Early, On Time or Late.

I am looking to


Summarize the on time delivery performance per supplier from sheet 2 “Sup OTD”
Pull or push the summarised on time delivery performance through to Sheet 1 “Supplier Register” for the relevant supplier.


Sample work book link


https://drive.google.com/open?id=1CB...nzcY_pjzImnW_v


Notes.
If it makes things easier Early and On Time can be changed to be on time.


Best Regards


Mark
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Your link does not lead to a workbook.
 
Upvote 0
In the sheet SUP OTD I have added a column where if there is late delivery I put -1, early delivery +1.

In the sheet Supplier Register, in the column AB (Condition_5(OTD)) I have put a formula to calculate the % late deliveries as a KPI. Maybe you want it differently, then you need to adjust the formula.
The formula is:

=SUMPRODUCT(('SUP OTD'!N2:N31<0)*('SUP OTD'!A2:A31=A4))/COUNTIF('SUP OTD'!A2:A31,A4)

Make sure that the company names in both sheets are identical (in your example sheet one of the tables had an extra space in the names).

The formula works as follows:
The Sumproduct part counts the number of cells in column N where the value is less than 0, and the value in column A is the same as the company name in A4.
This then is divided by the total number of deliveries by that company, giving you a % late deliveries.


File can be found here:
https://drive.google.com/file/d/1UU60EbRD9eMBay_Y1WQZYilihtTKFsaW/view?usp=sharing
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top