Here's another way (pretty much the same just using some different
techniques).

Assume this table is in Sheet2. A1:C1 are the column headers.

material thk lbs/sqft
cs 3/16 7.66
cs 1/4 10.2
cs 5/16 12.8
cs 3/8 15.3
cs 7/16 17.9
ss 14 ga 3.15
ss 10 ga 5.67
ss 3/16 8.58

The actual data is in A2:C9

Create some named ranges:

Material - refers to: =Sheet2!$A$2:$A$9
Thickness - refers to: =Sheet2!$B$2:$B$9
Weight - refers to: =Sheet2!$C$2:$C$9

On Sheet1 in A1:C1 are these headers:

Material, Thickness, lbs/sqft

Setup a drop down for Material:
Select cell A2
Goto DataValidation
Allow: List
Source: CS,SS
OK

Setup a drop down for Thickness:
Select cell B2
Goto DataValidation
Allow: List
Source:
=OFFSET(INDEX(thickness,1),MATCH(A2,material,0)-1,,COUNTIF(material,A2))
OK

Formula in C2 to return the corresponding weight:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=IF(ISNA(MATCH(1,(material=A2)*(thickness=B2),0)), "",INDEX(weight,MATCH(1,(material=A2)*(thickness=B 2),0)))

Biff

"vencopbrass"
wrote in message
...

I need to get some values from a list but I don't want to sort or filter
the list. I want a user in input some values and I want to obtain a
value in the list based on what was entered. Then I want to use that
value in other calculations. Also the list is formatted like this:

material thk lbs/sqft
cs 3/16 7.66
cs 1/4 10.2
cs 5/16 12.8
cs 3/8 15.3
cs 7/16 17.9
ss 14 ga 3.15
ss 10 ga 5.67
ss 3/16 8.58

and I want to get the lbs/sqft based on the first 2 columns.
So the input would be cs and 5/16 I need to get 12.8 and put that into
a cell where I can use it. Also I would prefer the user to not see this
list.

I would like to have a pull down for the first column and then based on
what is chosen have a pull down for the second column but have only the
values shown that correspond to the first column. then when a value is
chosen in column 2 the correct value for column 3 would appear.
Is this possible?? I have pulled my hair out trying.


--
vencopbrass
------------------------------------------------------------------------
vencopbrass's Profile:
http://www.excelforum.com/member.php...o&userid=31868
View this thread: http://www.excelforum.com/showthread...hreadid=515971