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 wrote:
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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote:
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.