Invoice Quotation

[Shabbo]

Dear Sir,

I wanted to make quotation for my customers as per attached excel sheet.

1) If the container size is 20 ft and weight from 1 to 14 then Invoice amount should be 18000 into no of container

2) If the container size is 20 ft and weight from 14.1 to 22.5 then Invoice amount should be 20400 no of container

3) If the container size is 20 ft and weight from 22.6 to 25 then Invoice amount should be 22300 no of container

4) If the container size is 40 ft and weight from 1 to 19 then Invoice amount should be 22550 into no of container

5) If the container size is 40 ft and weight from 19.1 to 21 then Invoice amount should be 24450 no of container

6) If the container size is 40 ft and weight from 21.1 to 25 then Invoice amount should be 27500 no of container

7) If the container size is 40 ft and weight from 25.1 to 30 then Invoice amount should be 31000 no of container

8) If the container size is Tank then Invoice amount should be 22800 into no of container.

9) If the container size is LCL and weight from 1 to 3.5 then Invoice amount should be 7000 into no of container.

10) If the container size is LCL and weight from 3.6 and above then Invoice amount should be 8500 into no of container

 

[vletm]

As You wrote ...

 

[bosco_yip]

Try,

1] Add one Quotation Sheet (content only for checking purpose)

2] Price List Sheet with small adjusting.

3] "Quotation sheet", Rate column F5, formula copy down :

=IF(B5="","",LOOKUP(9^9,CHOOSE(MATCH(B5,{"20ft";"40ft";"Tank";"LCL"},0),LOOKUP(C5,PriceList!$C$3:$C$5,PriceList!$E$3:$E$5),LOOKUP(C5,PriceList!$C$6:$C$9,PriceList!$E$6:$E$9),PriceList!$E$10,LOOKUP(C5,PriceList!$C$11:$C$12,PriceList!$E$11:$E$12))))

4] "Quotation sheet", Total column G5, formula copy down :

=IF(B5="","",D5*F5)

Regards

 

[rahulshewale1]

hii ,

please find attached solution using vlookup...


Thanks
Rahul shewale