| Freight Box Program Draft 2.xlsx | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | ZONE | Freight Box Shipping | Zone Shipping cost | Current Fuel Surcharge | Number of Stores in Zone | Freight Box Program Number | Freight Box program Cost total | Freight Box Program retail total | Freight Box program profit total | ||
| 2 | 7 | Zone Cost Economy | $ 280.00 | 48.90% | 27 | Program 1 | DATA RETURN HERE | ||||
| 3 | - | #N/A | |||||||||
| 4 | - | #N/A | |||||||||
| 5 | - | #N/A | |||||||||
| 6 | - | #N/A | |||||||||
| 7 | - | #N/A | |||||||||
| 8 | - | #N/A | |||||||||
| 9 | - | #N/A | |||||||||
| 10 | - | #N/A | |||||||||
| 11 | - | #N/A | |||||||||
| 12 | - | #N/A | |||||||||
| 13 | - | #N/A | |||||||||
| 14 | - | #N/A | |||||||||
Cost Roll up | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2 | C2 | =INDEX('Programs available'!K3:L9,MATCH(A2,'Programs available'!J3:J9,0),MATCH(B2,'Programs available'!$K$2:$L$2,0)) |
| C3 | C3 | =INDEX('Programs available'!K3:L9,MATCH(A3,'Programs available'!J3:J9,0),MATCH(B3,'Programs available'!$K$2:$L$2,0)) |
| C4 | C4 | =INDEX('Programs available'!K3:L9,MATCH(A4,'Programs available'!J3:J9,0),MATCH(B4,'Programs available'!$K$2:$L$2,0)) |
| C5 | C5 | =INDEX('Programs available'!K3:L9,MATCH(A5,'Programs available'!J3:J9,0),MATCH(B5,'Programs available'!$K$2:$L$2,0)) |
| C6 | C6 | =INDEX('Programs available'!K3:L9,MATCH(A6,'Programs available'!J3:J9,0),MATCH(B6,'Programs available'!$K$2:$L$2,0)) |
| C7 | C7 | =INDEX('Programs available'!K3:L9,MATCH(A7,'Programs available'!J3:J9,0),MATCH(B7,'Programs available'!$K$2:$L$2,0)) |
| C8 | C8 | =INDEX('Programs available'!K3:L9,MATCH(A8,'Programs available'!J3:J9,0),MATCH(B8,'Programs available'!$K$2:$L$2,0)) |
| C9 | C9 | =INDEX('Programs available'!K3:L9,MATCH(A9,'Programs available'!J3:J9,0),MATCH(B9,'Programs available'!$K$2:$L$2,0)) |
| C10 | C10 | =INDEX('Programs available'!K3:L9,MATCH(A10,'Programs available'!J3:J9,0),MATCH(B10,'Programs available'!$K$2:$L$2,0)) |
| C11 | C11 | =INDEX('Programs available'!K3:L9,MATCH(A11,'Programs available'!J3:J9,0),MATCH(B11,'Programs available'!$K$2:$L$2,0)) |
| C12 | C12 | =INDEX('Programs available'!K3:L9,MATCH(A12,'Programs available'!J3:J9,0),MATCH(B12,'Programs available'!$K$2:$L$2,0)) |
| C13 | C13 | =INDEX('Programs available'!K3:L9,MATCH(A13,'Programs available'!J3:J9,0),MATCH(B13,'Programs available'!$K$2:$L$2,0)) |
| C14 | C14 | =INDEX('Programs available'!K3:L9,MATCH(A14,'Programs available'!J3:J9,0),MATCH(B14,'Programs available'!$K$2:$L$2,0)) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| F2:F14 | List | Program 1, Program 2, Program 3, Program 4, Program 5 |
| A2:A10 | List | ='Programs available'!$J$3:$J$9 |
| B2:B14 | List | ='Programs available'!$K$2:$M$2 |
| A11:A29 | List | ='Programs available'!#REF! |
| Freight Box Program Draft 2.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | |||
| 1 | Program 1 | Number of kits | Cost of kit | Total Cost Kits | MSRP | Total Cost MSRP | ||||
| 2 | ell5 | 8 | $ 1.00 | $ 8.00 | $ 2.00 | $ 16.00 | ||||
| 3 | ell10 | 20 | $ 2.00 | $ 40.00 | $ 3.00 | $ 60.00 | ||||
| 4 | ell20 | $ 3.00 | $ - | $ 4.00 | $ - | |||||
| 5 | Totals: | 28 | $ 48.00 | $ 76.00 | Profit | $ 28.00 | ||||
| 6 | ||||||||||
| 7 | Program 2 | Number of kits | Cost of kit | Total Cost Kits | MSRP | Total Cost MSRP | ||||
| 8 | ell5 | 5 | $ 1.00 | $ 5.00 | $ 2.00 | $ 10.00 | ||||
| 9 | ell10 | 11 | $ 2.00 | $ 22.00 | $ 3.00 | $ 33.00 | ||||
| 10 | ell20 | 5 | $ 3.00 | $ 15.00 | $ 4.00 | $ 20.00 | ||||
| 11 | Totals: | 21 | $ 42.00 | $ 63.00 | Profit | $ 21.00 | ||||
| 12 | ||||||||||
| 13 | Program 3 | Number of kits | Cost of kit | Total Cost Kits | MSRP | Total Cost MSRP | ||||
| 14 | ell5 | 7 | $ 1.00 | $ 7.00 | $ 2.00 | $ 14.00 | ||||
| 15 | ell10 | 12 | $ 2.00 | $ 24.00 | $ 3.00 | $ 36.00 | ||||
| 16 | ell20 | 4 | $ 3.00 | $ 12.00 | $ 4.00 | $ 16.00 | ||||
| 17 | Totals: | 23 | $ 43.00 | $ 66.00 | profit | $ 23.00 | ||||
| 18 | ||||||||||
| 19 | Program 4 | Number of kits | Cost of kit | Total Cost Kits | MSRP | Total Cost MSRP | ||||
| 20 | ell5 | 6 | $ 1.00 | $ 6.00 | $ 2.00 | $ 12.00 | ||||
| 21 | ell10 | 12 | $ 2.00 | $ 24.00 | $ 3.00 | $ 36.00 | ||||
| 22 | ell20 | 5 | $ 3.00 | $ 15.00 | $ 4.00 | $ 20.00 | ||||
| 23 | Totals: | 23 | $ 45.00 | $ 68.00 | profit | $ 23.00 | ||||
| 24 | ||||||||||
| 25 | Program 5 | Number of kits | Cost of kit | Total Cost Kits | MSRP | Total Cost MSRP | ||||
| 26 | ell5 | 13 | $ 1.00 | $ 13.00 | $ 2.00 | $ 26.00 | ||||
| 27 | ell10 | 23 | $ 2.00 | $ 46.00 | $ 3.00 | $ 69.00 | ||||
| 28 | ell20 | $ 3.00 | $ - | $ 4.00 | $ - | |||||
| 29 | Totals: | 36 | $ 59.00 | $ 95.00 | profit | $ 36.00 | ||||
Programs available | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B5,F29,D29,B29,F23,D23,B23,F17,D17,B17,F11,D11,B11,F5,D5 | B5 | =SUM(B2:B4) |
| D2:D4,D26:D28,D20:D22,D14:D16,D8:D10 | D2 | =SUM(B2*C2) |
| F2:F4,F26:F28,F20:F22,F14:F16,F8:F10 | F2 | =SUM(B2*E2) |
| H5,H29,H23,H17,H11 | H5 | =F5-D5 |
Trying to populate the cost of the program on the cost roll up tab. I dont even know if excel can do this or if there's a better way. I want to populate G2 on the cost roll up based on referencing the program selected in F2. I want the information in F2 to reference the programs tab to search for the matching name from F2 and populate the highlighted total data based on the program name. Best i can tell is match offset from googling but this is way above my excel savvy.
please help
