Problem understanding dependant lookups

[dave.cuthill]

I have been reading examples on how to set up dependant lookups but I
am not getting it. I have a table of information the includes pipe OD,
weight, and ID. The is a unique ID (among other things) for each OD and
weight selection.

OD Wt ID
60 10 50
60 20 51
60 30 52
73 20 70
73 30 71
73 40 72


What I would like to do is have a validation list that contains the
possible OD selections, once the OD is selected it would automatically
change the available selections for the weight (Wt) validation list.
Once the OD and wt are selected the ID is displayed. If the OD was
reselected then the wt and ID shoold also be reset.

This looks so easy to do on the surface of things but I am not getting
it.

Thanks for any help.

 

[paul]

i can email you a forms combo box example.Is that your real email addy?

 

[dave.cuthill]

Thanks

The address may still be valid - but I do not want to use a forms combo
box - I want to do it in a cell.

 

[Max]

Here's a play which uses INDIRECT to read defined names ..

A sample construct is available at:
http://www.savefile.com/files/2678773
Dependent DVs via INDIRECT n Defined Names.xls

Assume this ref table is in K1:M7

OD Wt ID
60 10 50
60 20 51
60 30 52
73 20 70
73 30 71
73 40 72

Create the following defined names first
(via Insert > Name > Define):

ID_60 =Sheet1!$M$2:$M$4
ID_73 =Sheet1!$M$5:$M$7
W_60 =Sheet1!$L$2:$L$4
W_73 =Sheet1!$L$5:$L$7

Then create the DVs for OD, Wt & ID

For OD:
Select A2:A4
Click Data > Validation
Allow: List
Source: 63, 70
Click OK

For Wt:
Select B2:B4
Click Data > Validation
Allow: List
Source: =INDIRECT("W_"&A2)
Click OK

For ID:
Select C2:C4
Click Data > Validation
Allow: List
Source: =INDIRECT("ID_"&A2)
Click OK

Test the DVs out. They should work as required, i.e. the DV droplists for Wt
and ID will display depending on what's selected for OD

 

[Herbert Seidenberg]

Arrange your data like this:
OD Wt ID Wt2
60 10 50 10
60 20 51 20
60 30 52 30
73 20 70
73 30 71
73 40 72

hit1 hit2 hit3
60 20 51

Select the 7x4 array and
Insert > Name > Create > Top Row
Select the 2x3 array and do the same.
Enter this formula below Wt2 and copy down:
=IF(OD=hit1,Wt,"")
Select the cell below the header hit1 and
Data > Validation > List > Source =OD
Select the cell below the header hit2 and
Data > Validation > List > Source =Wt2
Select the cell below the header hit3 and enter
=SUMPRODUCT((hit1=OD)*(hit2=Wt)*ID)

 

[dave.cuthill]

The ID is totally dependent on the OD and Wt selections so it does not
need a look up. How would I change things so that once OD and Wt have
been selected the corresponding ID appears in a predesignated cell?
So in my table if an OD of 60 and a Wt of 20 was selected then the ID
should be 50 and nothing else.

Cell 1 Lookup for OD
Cell 2 Lookup for Wt (based on OD selection)
Cell 3 value of ID dependent on selections in Cell 1 and 2.

David

 

[Max]

The ID is totally dependent on the OD and Wt selections so it does not
need a look up. How would I change things so that once OD and Wt have
been selected the corresponding ID appears in a predesignated cell?
So in my table if an OD of 60 and a Wt of 20 was selected then the ID
should be 50 and nothing else.

Cell 1 Lookup for OD
Cell 2 Lookup for Wt (based on OD selection)
Cell 3 value of ID dependent on selections in Cell 1 and 2.

No problem, think we can use an array formula
to extract IDs based on the DV selections of OD and Wt

Based on the earlier sample ..
Clear* the DVs from C2:C4, then ..
*select C2:C4, click Data > Validation > Clear All > OK

Put in the formula bar for C2
and array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):
=IF(OR(A2="",B2=""),"",INDEX($M$2:$M$7,MATCH(1,($K$2:$K$7=A2)*($L$2:$L$7=B2),0)))
Copy C2 down to C4

Revised sample available at:
http://www.savefile.com/files/5427869
Dependent DVs via INDIRECT n Defined Names_1.xls

 

[dave.cuthill]

Thanks you have been very helpful - this appears to achieve what I was
trying to do. Is there a way using functions that allows the wt and id
values to be reset to null if the od is changed to a new value. I know
how to do this in code but was wondering if there where other options.

 

[dave.cuthill]

Thanks you have been very helpful - this appears to achieve what I was
trying to do. Is there a way using functions that allows the wt and id
values to be reset to null if the od is changed to a new value. I know
how to do this in code but was wondering if there where other options.

 

[Max]

Thanks you have been very helpful - this appears to achieve what I was
trying to do.

You're welcome !

Is there a way using functions that allows the wt and id
values to be reset to null if the od is changed to a new value. I know
how to do this in code but was wondering if there where other options.

Don't think so, I'm afraid ..