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

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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