obtaining data from a list

[vencopbrass]

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.

[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. 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 Data>Validation. 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

[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

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
news:[email protected]...
>
> 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
>

[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

[SteveG]

I'm glad you got what you needed.


Steve