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

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. One
for the Material options, one for the corresponding Material thk's (I
assume thickness). Then you'll need to create a table that you can
return the lbs/sqft from based on the Material and thk columns. I
assume you need to apply both conditions since you may have some
materials that are of the same thickness but weigh less.

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

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

In cell B2 go to data validation again and Allow: List. In the Source
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 different
materials only.

Set up a table for your lbs/sqft lookup. In the first column put the
Materials, in the second your thicknesses and then the corresponding
lbs/sqft in the third. Where you want the lbs/sqft to appear (I used
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,

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=515971

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

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


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

I'm glad you got what you needed.


Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=515971