pricing problems

[Guest]

I need to enter a PRODUCT TYPE, WIDTH and HEIGHT for Window coverings. I have
pricing grids, but i do not know how to link the grids to another form where
i input the product type, width, height. I want the product to find the grid
that matches, then on that grid i need the width to go to the column that
matches (type in 36 and it goes to the column of 36-42), then the height to
go to the row that matches its measurements? Can anyone please help me on
this?

 

[Bernie Deitrick]

Trent,

Post a couple example tables, along with the cell range addresses where they
are entered, and it will be easier to help you.

Bernie

 

[Guest]

thank you for responding. I hope this is what you are requesting.
EXAMPLE of pricing sheet:
wood shutters
12" 18" 24" 30" 38"
12" 152 185 192 201 245
18" 185 215 255 301 299
24" 192 255 295 345 397
30" 201 264 315 396 425
38" 245 301 345 425 526

this example is on a sheet labeled 'wood shutters' in workbook labeled
'pricing', from another workbook labeled 'Our Costs' we are wanting to input
the type of product (so to reference the 'price' sheet) then the width
(indicating the column headings) and the height (indicated by the row
headings) to give us a result. Example:

PRODUCT WIDTH X HEIGHT PRICE
wood shutters 18 x 30 reference from wood shutter sheet
vinyl shutters 21 x 32 EXAMPLE '345'
etc. 15 x 32 EXAMPLE '301'

the example above shows in the product column we have vinyl shutters also
listed. We would like to have a scoll down box to include all products that
we have pricing sheets for, so that when we select a specific product it
would reference the respective pricing sheet.

 

[Guest]

so every product has its own unique specific worksheet name?

 

[Guest]

yes, but there are a total of 38 different products.

 

[Guest]

thats ok,as long as each product/worksheet has a unique name.I am in NZ so i
will respond tonight(our time)Watch this space!!!!

 

[Guest]

will you be using measurements that match the measurements on your pricing
sheet or will you want the lookup to use the next largest measurement??say
your actual measurement is 32 but the next closest price measurement is 36(i
assume you wouldnt want to go to the smaller measurement of say 30)

 

[Guest]

paul,
you are correct, we would like it to use the next largest measurement.
trent

 

[Guest]

ok here is how i did it
you have a drop down box to refer to all of your products,and linked to that
drop down box you have three cells to name the ranges you need to do your
bidding
1) each product price list is arranged in descending order for height and
width ie from largest to smallest,and the entire range is named These names
correspond exactly to your product list for your drop down box choices
when you choose "wood" your first special cell shows wood(say this cell is a2)
your second special cell shows woodrow a3(ie=a2&"row")
your third special cell shows woodcol a4(ie=a2&"col")
on each product page you name the column with your heights(or widths) as say
woodcol and you name the row with the widths or heights as woodrow,and so on
for vinyl301,vinyl301col,vinyl301row,etc etc.
on your pricing sheet ie the one with the drop down box set up two cells
for width input and height input we will call them height and width(not names
just a label instead of a cell reference)
your lookup formula will look like
this=INDEX(INDIRECT(A2),MATCH(width,INDIRECT(width range ref cell say
a3),-1),MATCH(height,INDIRECT(height range ref cell say a4),-1)).If you post
your email addy here i will mail you a example.

 

[Guest]

Paul, thank you for your time. I would appreciate an email with an example.
That would be (e-mail address removed)

trent