Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
" wrote:
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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
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 wrote: " wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
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 wrote: " wrote: 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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem understanding dependant lookups
" wrote:
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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent Help Required on Excel Macro Problem | Excel Discussion (Misc queries) | |||
Problem With Reference Update | Excel Worksheet Functions | |||
Copy an Drag cell Formula Problem | Excel Discussion (Misc queries) | |||
problem office assistant | Excel Discussion (Misc queries) | |||
Paper Tray selection Problem, | Excel Discussion (Misc queries) |