This is my dilema and I cannot find any answers anywhere.

I want to create a spreadsheet where the following values...

Width DIA Pitch
1 5 0.1
2 4 0.2
3 3 0.3
4 2 0.4
5 1 0.5

For each and every single scenario possible there is a different price (i
haven't listed them) how would I create a spreadsheet where i have three drop
down boxes (i know how to do these) and when i select a value from the drop
down box it displays a price that conforms with that scenario? So say Width
=1 DIA=5 PITCH=0.1 = SAY $1 then i change it too Width = 2 DIA=5 Pitch= 0.1
this then = $2 so on and so forth, is this actually possible and is it a BIG
process or is there a really easy way!!

I probably haven't explained this well at all and I do apologise.


Thanks

If there's a linear relationship between the 3 specs and the price then you
might be able to do this without creating a table. Otherwise, you'll have to
create a table that lists all the possible combinations along with the
prices.

......W.....D.....Pit.....Price
.......1......1.....0.1......10
.......1......1.....0.2......12
.......1......1.....0.3......15
.......1......2.....0.1......11
.......1......2.....0.2......17
.......1......2.....0.3......22
etc
etc

Find the price for:

W = 1, D = 2, Pit = 0.2

With the above table in the range A2:D7 (A1:D1= column headers).

Criteria:

A10 = 1 (W)
B10 = 2 (D)
C10 = 0.2 (Pit)

=SUMPRODUCT(--(A2:A7=A10),--(B2:B7=B10),--(C2:C7=C10),D2:D7)

Result = 17

--
Biff
Microsoft Excel MVP


"CHR1S" wrote in message
...
This is my dilema and I cannot find any answers anywhere.

I want to create a spreadsheet where the following values...

Width DIA Pitch
1 5 0.1
2 4 0.2
3 3 0.3
4 2 0.4
5 1 0.5

For each and every single scenario possible there is a different price (i
haven't listed them) how would I create a spreadsheet where i have three
drop
down boxes (i know how to do these) and when i select a value from the
drop
down box it displays a price that conforms with that scenario? So say
Width
=1 DIA=5 PITCH=0.1 = SAY $1 then i change it too Width = 2 DIA=5 Pitch=
0.1
this then = $2 so on and so forth, is this actually possible and is it a
BIG
process or is there a really easy way!!

I probably haven't explained this well at all and I do apologise.


Thanks

Say you create your data list in Columns W, X, Y, and Z.
W = Width
X = Dia
Y = Pitch
Z = Price

Say from W2 to Z100.

Say your dropdowns are Columns A2, B2, and C2.

Enter this formula in D2:

=Sumproduct((W2:W100=A2)*(X2:X100=B2)*(Y2:Y100=C2) *Z2:Z100)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"CHR1S" wrote in message
...
This is my dilema and I cannot find any answers anywhere.

I want to create a spreadsheet where the following values...

Width DIA Pitch
1 5 0.1
2 4 0.2
3 3 0.3
4 2 0.4
5 1 0.5

For each and every single scenario possible there is a different price (i
haven't listed them) how would I create a spreadsheet where i have three
drop
down boxes (i know how to do these) and when i select a value from the
drop
down box it displays a price that conforms with that scenario? So say
Width
=1 DIA=5 PITCH=0.1 = SAY $1 then i change it too Width = 2 DIA=5 Pitch=
0.1
this then = $2 so on and so forth, is this actually possible and is it a
BIG
process or is there a really easy way!!

I probably haven't explained this well at all and I do apologise.


Thanks