lookup and distribute the qty based on capacity

[ExcelNewbie2020]

i have monthly sales table (jan to mar).. i need a formula to place it into the summary table..
however, need to consider the capacity.. like for example sold qty for apple in the month of jan is 120 but the first truck capacity is only 100. the remaining qty (20) will be distributed to the next truck..

see table (blue is the expected result)

Excel.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1				formula here							sold
2	fruits	truck	capacity	jan	feb	mar					jan	qty
3	apple	1001	100	100							apple	120
4	apple	1002	120	20	100						grapes	80
5	apple	1003	80								orange	110
6	grapes	1004	100	80	20
7	grapes	1005	80		30	20					feb
8	orange	1006	100	100							apple	100
9	orange	1007	120	10		100					grapes	50
10	orange	1008	90
11
12											mar
13											grapes	20
14											orange	100
15
16
Sheet12

 

[bobaol]

Try this... notice every fruit starts at row 11, 21, 31, etc... it is much easier to work with a relative reference. second, the formulas use a relative reference at row 10, row 10 being the quantity sold. here are the formulas, and an image of the result. Please scroll down. Also, if you want to pretty this up more, you can add another sheet3, and use sheet3 to reference only the items you want to show. cheers.

''cell A1

jan

qty

feb

qty

mar

qty

=ROW()

apple

120

apple

100

apple

0

=ROW()

grapes

80

grapes

50

grapes

20

=ROW()

orange

110

orange

0

orange

100

=ROW()

=ROW()

=ROW()

=ROW()

Jan

Feb

Mar

=ROW()

sold

sold

sold

=ROW()

fruits

truck

capacity

into truck

=D2

capacity

into truck

=F2

capacity

into truck

=H2

=ROW()

apple

1001

100

=IF(D11-F10<0,D11,F10)

=F10-E11

100

=IF(H11-J10<0,H11,J10)

=J10-I11

100

=IF(L11-N10<0,L11,N10)

=N10-M11

=ROW()

apple

1002

120

=IF(D12-F11<0,D12,F11)

=F11-E12

120

=IF(H12-J11<0,H12,J11)

=J11-I12

120

=IF(L12-N11<0,L12,N11)

=N11-M12

=ROW()

apple

1003

80

=IF(D13-F12<0,D13,F12)

=F12-E13

80

=IF(H13-J12<0,H13,J12)

=J12-I13

80

=IF(L13-N12<0,L13,N12)

=N12-M13

=ROW()

=ROW()

=ROW()

=ROW()

=ROW()

=ROW()

=ROW()

into truck

=D3

into truck

=F3

into truck

=H3

=ROW()

grapes

1004

100

=IF(D21-F20<0,D21,F20)

=F20-E21

100

=IF(H21-J20<0,H21,J20)

=J20-I21

100

=IF(L21-N20<0,L21,N20)

=N20-M21

=ROW()

grapes

1005

80

=IF(D22-F21<0,D22,F21)

=F21-E22

80

=IF(H22-J21<0,H22,J21)

=J21-I22

80

=IF(L22-N21<0,L22,N21)

=N21-M22

=ROW()

=IF(D23-F22<0,D23,F22)

=F22-E23

=IF(H23-J22<0,H23,J22)

=J22-I23

=IF(L23-N22<0,L23,N22)

=N22-M23

=ROW()

=ROW()

=ROW()

=ROW()

=ROW()

=ROW()

=ROW()

into truck

=D4

into truck

=F4

into truck

=H4

=ROW()

orange

1006

100

=IF(D31-F30<0,D31,F30)

=F30-E31

100

=IF(H31-J30<0,H31,J30)

=J30-I31

100

=IF(L31-N30<0,L31,N30)

=N30-M31

=ROW()

orange

1007

120

=IF(D32-F31<0,D32,F31)

=F31-E32

120

=IF(H32-J31<0,H32,J31)

=J31-I32

120

=IF(L32-N31<0,L32,N31)

=N31-M32

=ROW()

orange

1008

90

=IF(D33-F32<0,D33,F32)

=F32-E33

90

=IF(H33-J32<0,H33,J32)

=J32-I33

90

=IF(L33-N32<0,L33,N32)

=N32-M33

=ROW()

=ROW()

=ROW()

=ROW()

=ROW()