Hello there, I have a very specific request. I want to minimize my employees' mistakes when filling some transport way-bills. My idea is to automatically fill Clients, Number of pallets per client and Division by selecting the Transport company. The data will be extracted from a source transport schedule. However, this transport schedule has certain format which I cannot change. I'll illustrate with some images what i want to do.

This is the transport schedule format:
\1
Here the employees fill the transport company, the client and the quantity of pallets per Division for every shipment. The column qty count the number of rows that the client should have in the way bill.

This is the way-bill. What I want to do is to fill automatically some of the columns (marked in red) in the following sheet by choosing the transport company (marked in yellow).
\1

It's tricky is because we can have shipments from different divisions to the same client. In the example - Client 1 (shipped by Company 1) has shipment from Division 1, but also has from Divisions 3 and 6. Therefore we have to fill 3 rows (the qty column from the schedule) with Client 1 - each for every division, and extract the pallet quantity for each division.
Let's continue - according to the source transport schedule Company 1 (chosen in the yellow tab) also ships to Clients 2, 4 and 5. They have their Divisions filled with the proper pallet quantities.

\1

Another example we have chosen Transport Company 4. The fields are filled with the proper information extracted from the transport schedule.

I have done these images by hand. My goal is to automate the process. When s.o. fills the transport schedule and then chooses the transport company in the way-bill the columns in red have to fill in automatically. Can this be done? Some tricky array formulas, lookup, somehow?