obtaining data from a list

V

vencopbrass

I need to get some values from a list but I don't want to sort or filte
the list. I want a user in input some values and I want to obtain
value in the list based on what was entered. Then I want to use tha
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 int
a cell where I can use it. Also I would prefer the user to not see thi
list.

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

SteveG

Absolutely. You have a lot of questions here so this will be long.
First, to set up your drop downs, you should create some lists. On
for the Material options, one for the corresponding Material thk's (
assume thickness). Then you'll need to create a table that you ca
return the lbs/sqft from based on the Material and thk columns.
assume you need to apply both conditions since you may have som
materials that are of the same thickness but weigh less.

I used the array of A1:C9 for this example. I put a list o
thicknesses based on material cs in J3:J7 leaving J2 blank and ss i
K3:K5 leaving K2 blank. In
N1 and N2 I have cs and ss for te materials list.

In cell A2, select Data>Validation. On the Settings tab, select Allow
List from the option. In the Source box, N1:N2. Click OK. This i
your materials drop down.

In cell B2 go to data validation again and Allow: List. In the Sourc
box type the following formula.

=IF(A2="cs",$J$2:$J$7,IF(A2="ss",$K$2:$K$5))

This will direct the drop down to refer to your lists for the differen
materials only.

Set up a table for your lbs/sqft lookup. In the first column put th
Materials, in the second your thicknesses and then the correspondin
lbs/sqft in the third. Where you want the lbs/sqft to appear (I use
column C next to the thk column), use SUMRODUCT to pull in the number.
In O1:Q9 I put my table. The SUMPRODUCT would be:

=IF(SUMPRODUCT(--($O$1:$O$8=A2),--($P$1:$P$8=B2),($Q$1:$Q$8))=0,"",SUMPRODUCT(--($O$1:$O$8=A2),--($P$1:$P$8=B2),($Q$1:$Q$8)))

Copy this down the list. You could also use lookup formulas as well.
Hopefully this is what you were looking for.



Cheers,

Stev
 
B

Biff

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
Click to expand...

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 Data>Validation
Allow: List
Source: CS,SS
OK

Setup a drop down for Thickness:
Select cell B2
Goto Data>Validation
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=B2),0)))

Biff

"vencopbrass" <[email protected]>
wrote in message
 
V

vencopbrass

Thank you so much. You have helped me immensely! I was able to get it to
work on my spreadsheet using steve's way. I tried it first using biff
method but I kept getting errors when I tried the offset command in the
data validation. I don't know what I was screwing up but anyways its
working now. Again thanks to you both!
Lisa
 
Top