Tiered Pricing Based on Customer

[stevew1990]

Hello,

Bit stuck. I need to have a sheet with a list of customer account numbers with that customers "pricing tier". a sheet with a price list and X number of tiers (ideally 3-4). And the front customer facing form. Obviously the first 2 sheets would be hidden/locked out of view. Would like to be able to enter the customers account number on the front screen and that customers tier of pricing populates in the list (would then lock/hide the account number and tier cells so that they dont have visibility of this)

No problem looking up the tier from the account number or other vlookups. Just struggling to pull the correct price based on tier. Hopefully someone can help here!

Customer Tier Listing:

[TABLE="width: 223"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]Tier[/TD]
[/TR]
[TR]
[TD="align: right"]1001001[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1001002[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]1001003[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

Pricing:

[TABLE="width: 802"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Product Code [/TD]
[TD]Type[/TD]
[TD]Product description [/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Discount:[/TD]
[TD]0%[/TD]
[TD]5%[/TD]
[TD]10%[/TD]
[TD]15%[/TD]
[/TR]
[TR]
[TD]AB-CDE-1[/TD]
[TD]Type 1[/TD]
[TD]Product 1[/TD]
[TD]£3.68[/TD]
[TD="align: right"]£3.50[/TD]
[TD="align: right"]£3.31[/TD]
[TD="align: right"]£3.13[/TD]
[/TR]
[TR]
[TD]AB-CDE-2[/TD]
[TD]Type 2[/TD]
[TD]Product 2[/TD]
[TD]£5.03[/TD]
[TD="align: right"]£4.78[/TD]
[TD="align: right"]£4.53[/TD]
[TD="align: right"]£4.28[/TD]
[/TR]
[TR]
[TD]AB-CDE-3[/TD]
[TD]Type 3[/TD]
[TD]Product 3[/TD]
[TD]£3.60[/TD]
[TD="align: right"]£3.42[/TD]
[TD="align: right"]£3.24[/TD]
[TD="align: right"]£3.06[/TD]
[/TR]
[TR]
[TD]AB-CDE-4[/TD]
[TD]Type 1[/TD]
[TD]Product 4[/TD]
[TD]£4.95[/TD]
[TD="align: right"]£4.71[/TD]
[TD="align: right"]£4.46[/TD]
[TD="align: right"]£4.21[/TD]
[/TR]
[TR]
[TD]AB-CDE-5[/TD]
[TD]Type 2[/TD]
[TD]Product 5[/TD]
[TD]£3.60[/TD]
[TD="align: right"]£3.42[/TD]
[TD="align: right"]£3.24[/TD]
[TD="align: right"]£3.06[/TD]
[/TR]
[TR]
[TD]AB-CDE-6[/TD]
[TD]Type 3[/TD]
[TD]Product 6[/TD]
[TD]£4.95[/TD]
[TD="align: right"]£4.71[/TD]
[TD="align: right"]£4.46[/TD]
[TD="align: right"]£4.21[/TD]
[/TR]
[TR]
[TD]AB-CDE-7[/TD]
[TD]Type 1[/TD]
[TD]Product 7[/TD]
[TD]£3.51[/TD]
[TD="align: right"]£3.33[/TD]
[TD="align: right"]£3.16[/TD]
[TD="align: right"]£2.98[/TD]
[/TR]
[TR]
[TD]AB-CDE-8[/TD]
[TD]Type 2[/TD]
[TD]Product 8[/TD]
[TD]£3.78[/TD]
[TD="align: right"]£3.59[/TD]
[TD="align: right"]£3.40[/TD]
[TD="align: right"]£3.22[/TD]
[/TR]
</tbody>[/TABLE]


"Customer Facing" Prices:

[TABLE="width: 1384"]
<colgroup><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Product Code [/TD]
[TD]Type[/TD]
[TD]Product description [/TD]
[TD] Price EX VAT[/TD]
[TD] QTY[/TD]
[TD] Total[/TD]
[TD][/TD]
[TD]Account Number[/TD]
[TD]1001002[/TD]
[TD] Tier[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]AB-CDE-1[/TD]
[TD]Type 1[/TD]
[TD]Product 1[/TD]
[TD]£3.50[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD][/TD]
[TD]Customer Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB-CDE-2[/TD]
[TD]Type 2[/TD]
[TD]Product 2[/TD]
[TD]£4.78[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD][/TD]
[TD]Contact[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB-CDE-3[/TD]
[TD]Type 3[/TD]
[TD]Product 3[/TD]
[TD]£3.42[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD][/TD]
[TD]Contact Number[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB-CDE-4[/TD]
[TD]Type 1[/TD]
[TD]Product 4[/TD]
[TD]£4.71[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB-CDE-5[/TD]
[TD]Type 2[/TD]
[TD]Product 5[/TD]
[TD]£3.42[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB-CDE-6[/TD]
[TD]Type 3[/TD]
[TD]Product 6[/TD]
[TD]£4.71[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB-CDE-7[/TD]
[TD]Type 1[/TD]
[TD]Product 7[/TD]
[TD]£3.33[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AB-CDE-8[/TD]
[TD]Type 2[/TD]
[TD]Product 8[/TD]
[TD]£3.59[/TD]
[TD] [/TD]
[TD="align: right"]£0.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So i want to pull the customers correct price list just by entering the account number before we send them their current price list. This front page will probably be prettied up as more of an order form with logo and what not once working..

Let me know if you have any insights!

Cheers,
Steve

 

[stevew1990]

...Bump...

 

[stevew1990]

There must be someone out there who can help ?

Thanks!

 

[wideboydixon]

Book1
	A	B
1	Customer	Tier
2	1001001	1
3	1001002	2
4	1001003	3
CustomerTiers

Book1
	A	B	C	D	E	F	G
1	Products Code	Type	Product Description	1	2	3	4
2			Discount:	0%	5%	10%	15%
3	AB-CDE-1	Type 1	Product 1	£3.68	£3.50	£3.31	£3.13
4	AB-CDE-2	Type 2	Product 2	£5.03	£4.78	£4.53	£4.28
5	AB-CDE-3	Type 3	Product 3	£3.60	£3.42	£3.24	£3.06
6	AB-CDE-4	Type 1	Product 4	£4.95	£4.70	£4.46	£4.21
7	AB-CDE-5	Type 2	Product 5	£3.60	£3.42	£3.24	£3.06
8	AB-CDE-6	Type 3	Product 6	£4.95	£4.70	£4.46	£4.21
9	AB-CDE-7	Type 1	Product 7	£3.51	£3.33	£3.16	£2.98
10	AB-CDE-8	Type 2	Product 8	£3.78	£3.59	£3.40	£3.21
Pricing

Cell Formulas
Range	Formula
E3	=$D3*(1-E$2)
F3	=$D3*(1-F$2)
G3	=$D3*(1-G$2)

Book1
	A	B	C	D	E	F	G	H
1	Product Code	Type	Product Description	Price EX VAT	QTY	Total	Account Number	1001002
2	AB-CDE-1	Type 1	Product 1	£3.50		£0.00	Customer Name
3	AB-CDE-2	Type 2	Product 2	£4.78		£0.00	Contact
4	AB-CDE-3	Type 3	Product 3	£3.42		£0.00	Contact Number
5	AB-CDE-4	Type 1	Product 4	£4.70		£0.00
6	AB-CDE-5	Type 2	Product 5	£3.42		£0.00
7	AB-CDE-6	Type 3	Product 6	£4.70		£0.00
8	AB-CDE-7	Type 1	Product 7	£3.33		£0.00
9	AB-CDE-8	Type 2	Product 8	£3.59		£0.00
CustomerFacing

Cell Formulas
Range	Formula
B2	=VLOOKUP($A2,Pricing!$A$3:$G$10,2,FALSE)
C2	=VLOOKUP($A2,Pricing!$A$3:$G$10,3,FALSE)
D2	=VLOOKUP($A2,Pricing!$A$3:$G$10,3+VLOOKUP($H$1,CustomerTiers!$A$2:$B$4,2,FALSE),FALSE)
F2	=$D2*$E2

WBD

 

[stevew1990]

You absolute legend. Thanks so much

 

[wideboydixon]

You absolute legend. Thanks so much

You're welcome. Just remember to adjust the ranges and sheet names as necessary

Regards,

WBD