Advanced Business Spreadsheets

Customer Name:

Preferred CA Truck

1 2=Description 3=Quantity 4=Volume 5= $/Unit 6=Total Item# Description Quantity Volume (cf) $/unit Total

Total Order Discount 7=Discount Shipping 8=Shipping

Grand Total 9=Grand Total

Discounts =Discount!$B$1:$F$2 ItemList ='Item List'!$A$2:$D$19 Sched1 =Sched1!$A$2:$B$18 Sched2 =Sched2!$A$3:$B$7 Ship1 =Ship!$B$4:$D$8 Ship2 =Ship!$B$12:$D$16 States =States!$B$2:$D$52

1=Item#

2=Description

3=Quantity

4=Volume

5= $/Unit

6=Total

7=Discount

8=Shipping

9=Grand Total Sum of Total Order + Discount + Shipping

Freight Customer Type (regular, preferred) Ship to State abbreviation Ship Method (truck,rail,air,boat)

Find the Discount ==> =- HLOOKUP(F16,Discounts,2,TRUE) * F16 ==> - Discount * Total Order

Find the Discount ==> =INDEX((Ship1,Ship2),VLOOKUP(F5,States,3,FALSE),MATCH(F6,Ship!B3:D3,0),MATCH(F4,{"regular","preferred"},0))*D16 ==> - Discount * Total Order

Select the Column in the Table 2

=IF(ISBLANK(A9), "", C9* E9)

Setup the following Name Defined:

Choose the FALSE for Sched1 and TRUE for Sched2 ==> VLOOKUP(A9, ItemList, 3, FALSE)>1

VLOOKUP(IF(VLOOKUP(A9, ItemList, 3, FALSE) = 1, A9, C9),CHOOSE(VLOOKUP(A9, ItemList, 3, FALSE), Sched1,Sched2),2,VLOOKUP(A9, ItemList, 3, FALSE)>1)

Choose the Price Schedule Table ==> CHOOSE(VLOOKUP(A9, ItemList, 3, FALSE), Sched1,Sched2)

IF(VLOOKUP(A9, ItemList, 3, FALSE) = 1, A9, C9) ==> Sched 1 use Item# and Sched2 use Quantity

Find the Price Schedule ==> VLOOKUP(A9, ItemList, 3, FALSE)

Fishing Division Order Form

=IF(ISBLANK(A9),"",VLOOKUP(IF(VLOOKUP(A9, ItemList, 3, FALSE) = 1, A9, C9),CHOOSE(VLOOKUP(A9, ItemList, 3, FALSE), Sched1,Sched2),2,VLOOKUP(A9, ItemList, 3, FALSE)>1))

=IF(ISBLANK(A9)," ",VLOOKUP(A9,ItemList,2,FALSE))

Enter Item #

Enter Quantity

=IF(ISBLANK(A9), "", VLOOKUP(A9,ItemList,4,FALSE)*C9)