Creating a quotation form using another excel chart

[cait2002]

Hi, I am going to attempt to explain my question as simple as possible. Here goes...

My boss has asked me to create a sales quoation form in excel for our sales reps to use to quote our clients so they don't spend so much manual time pricing out our product. However I have a couple issues.

First a little business background as this may help. We are a window blind manufacturer. Our pricing is based on a suggested retail price (SRP) grid. The pricing is based on sizes.. width x height (these are custom made blinds, btw). The width numbers are across the Columns, and the height is listed down the Rows. The SRP values are filled in, in between the two.

Short example (hopefully the stay aligned when I submit this msg)...
.....18..24..30..36...42 .........
30 178 188 219 257 288
36 183 197 236 273 306
42 196 208 254 291 326
48 214 227 277 320 357
54 229 252 299 335 390

if someone orders a 25" x 45" blind you need to jump to the 30" width x 48" height which is SRP of 277. Now our sales reps then apply a discount value such as .20 (80% off SRP). This gives the customer cost as $55.40 before tax(es).

Hope your all with me so far. I'm not done yet.... lol

This particular sample chart is for Horizontal blinds. These prices include Standard valance but clients can upgrade to a Crown Valance (fancier profile). I have another list for that, shown below the above chart.

(Crown Valance surcharge example)
width..
18 24 30 36 42.........
26 35 44 52 61

If the client that bought the 25" x 45" blind wants crown then you would take 277 + 44 = 321 x .20 = $64.20 before tax(es).

Soooooooooo... with this information... I need to create a quote form where the sales rep enters the width in one cell, the height in the one beside it and excel take those two numbers rounds up to the greater or equal field to extract the SRP into another cell. Then I will have the rep enter the discount point value and it will generate the actual sale price.

I need this to do mulitple blinds then add the total at the bottom with sales tax etc.

A bit about me and excel. I have taken some excel classes and I am watching excel video classses online, so formulas are not foreign to me but I am no expert either, I am still pretty much a beginner/intermidiate beginner.

I have in the past taken our price list and copied & pasted (duplicated) it below the upper one, manipulated the lower section with formulas so that if you changed the discount value (in an assigned cell) it would recalculate the numbers to show the SRP (within the lower chart) however the sales rep still needs to navigate the chart to find the value. I don't want to do this as its not much of a time saver.

Problems....
1) need a way to calculate surcharges
2) every product (ie. Horizontals, Verticals, Roller Shades, etc..) all have their own price list so I will will need to import all the charts into this sheet and have a field where they choose the product so excel knows what chart (table) to look at.
3) I need the bottom of the excel sheet to end whereever the last blind is as there could be 5 blinds or 500 blinds in one quotation form (without inserting or hiding rows as needed).

Can anyone help me start figuring out how to lay this out and what some of the formulas would be or refer me to some online tutorials?

PS. Please reply in simple terminology. thanks

Your help and feedback is GREATLY appriciated!!!!

Thank you sooo much, in advance.

 

[cait2002]

*bump*

 

[alvinwlh]

Hi,

I assume you have a sheet called blind which store all your data:

sheets("blind") From A1:F6 (may be more but start from A1)
.....18..24..30..36...42 .........
30 178 188 219 257 288
36 183 197 236 273 306
42 196 208 254 291 326
48 214 227 277 320 357
54 229 252 299 335 390

sheets("Crown") From A1:E2 (may be more but start from A1)
18 24 30 36 42.........
26 35 44 52 61

Another separate sheet where
HR VT BL CR
25 45 C2 D2

c2:
=INDEX(Blind!$1:$1000,MATCH(VLOOKUP(B2,Blind!$A:$A,1,1),Blind!$A:$A,0)+1,MATCH(HLOOKUP(A2,Blind!$B$1:$F$1,1,1),Blind!$1:$1,0)+1)

D2:
=INDEX(Crown!$2:$2,1,MATCH(HLOOKUP(A2,Crown!$1:$1,1,1),Crown!$1:$1,0)+1)

Hope it helps
Thanks

 

[ridz89]

Hey man great tips! im looking for the same thing, could you please help me with one more thing? your formula works great heres what ive got

A1-B1-C1------D1---E1
25-45-Venitian-A ---??


so far my E2 =

=INDEX([Holland]Test!$1:$1000,MATCH(VLOOKUP(B2,[Holland]Test!$A:$A,1,1),[Holland]Test!$A:$A,0)+1,MATCH(HLOOKUP(A2,[Holland]Test!$B$1:$F$1,1,1),[Holland]Test!$1:$1,0)+1)

At the moment its only using A2 and B2 as criteria, I need it to use C2 and D2, cause its looking up the holland price list only.

I need it to look at a different price list if Blind Type changes and Category Changes too. Could you please help me with this?

many many thanks

new user ridz

 

[alvinwlh]

Hi,

What are your criteria for c1 and D1?
You said it only looks for holland price list. So where are the others? In the same sheet? Or different?

Alvin

 

[ridz89]

well the other price lists are on other sheets starting from a1 etc like you mentioned in your post. so i split up the price lists on other sheets in another workbook or i can import to same workbook (which ever you reckon).

not sure if this makes sense but theres a price list for the 5 different items in C1 (Holland,Verticle, Venitian etc...) and for every item in C1 theres a different price list in D1 ( Category A,B,C,D). IF some one chose items from a drop down box in C1 and selected a Letter (A,B,C,D) in D1 could it find the correct price list? and then match the other two criteria HR (A1) and VT (B1) and return the price.


...sorry for so many questions but also for your quote:

.....18..24..30..36...42 .........
30 178 188 219 257 288
36 183 197 236 273 306
42 196 208 254 291 326
48 214 227 277 320 357
54 229 252 299 335 390

If the VT and HR were say 10 and 10, which is under 18 and 30, it should select 178. but the index match you created does not select anything, everything else works perfect .


many many many thanks

ridz