Delivery Summary spreadsheet, A function to automatically updates Boxes Pending and Delivery Status.

[JLeech1992]

Hi,

I am in the middle of creating a spreadsheet to keep track of deliveries for my work,
It is nearly complete but there is a function I want to use, unfortunately I can't seem to figure it out.

Here is the template so far…

Returned by:

Boxes Requested:

Boxes Delivered:

Boxes Pending:

Delivery Status: (Drop Down)
Shipment Collected
Collection Failed
Incomplete Shipment
Completed Shipment

This will be a summary of my day-to-day deliveries I will receive.

The function I am trying to achieve:

Returned by:
Sender Name
Boxes Requested:
10
Boxes Delivered:
8
Boxes Pending:
2
Delivery Status: (Drop Down)
Incomplete Shipment
--------------------------------------------------------------------------
Returned by:
Sender Name
Boxes Requested:
10
Boxes Delivered:
10
Boxes Pending:
0
Delivery Status: (Drop Down)
Completed Shipment

• Once a shipment is delivered, I will update 'Boxes Delivered' and what I want it to do is to automatically update 'Boxes Pending' with the difference, I also want it to automatically update 'Delivery Status' to its current status as shown above.
• I have already applied the conditional formatting to the drop downs so whenever I change the status, that row changes color.

Any advice would be greatly appreciated.