Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Validation Lookup Table Help Please
sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is
selected it will show sheet2 column A choices, no biggie so far, but based on the list validation choice, I want it to return the value in the corresponding row in sheet2 column b in cell I3 IE: Sheet 2 Column A Column B Hard Drive 2 Monitor 3 keyboard 4 sheet 1 cell I3 list validation shows column a choices, I select hard drive it will put a 2 in cell I3 not Hard Drive???? Also I have looked at contextures code and still cannot figure this out, please tell me what i need to put in the code list validation bax, thanks so much |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Validation Lookup Table Help Please
You have to use match. If the table starts in row 1 then
=MATCH($A$1,$A$2:$A$100) if the table starts in some other row then =MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1 Match will give you the index number into the array. So if you know the row number of the first member (row(A10)) then you simply add the index return by match to the starting row number. "Kenny" wrote: sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is selected it will show sheet2 column A choices, no biggie so far, but based on the list validation choice, I want it to return the value in the corresponding row in sheet2 column b in cell I3 IE: Sheet 2 Column A Column B Hard Drive 2 Monitor 3 keyboard 4 sheet 1 cell I3 list validation shows column a choices, I select hard drive it will put a 2 in cell I3 not Hard Drive???? Also I have looked at contextures code and still cannot figure this out, please tell me what i need to put in the code list validation bax, thanks so much |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Validation Lookup Table Help Please
this does not work, the list validation needs to refernce sheet2 not sheet 1,
i get an error with your code "Joel" wrote: You have to use match. If the table starts in row 1 then =MATCH($A$1,$A$2:$A$100) if the table starts in some other row then =MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1 Match will give you the index number into the array. So if you know the row number of the first member (row(A10)) then you simply add the index return by match to the starting row number. "Kenny" wrote: sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is selected it will show sheet2 column A choices, no biggie so far, but based on the list validation choice, I want it to return the value in the corresponding row in sheet2 column b in cell I3 IE: Sheet 2 Column A Column B Hard Drive 2 Monitor 3 keyboard 4 sheet 1 cell I3 list validation shows column a choices, I select hard drive it will put a 2 in cell I3 not Hard Drive???? Also I have looked at contextures code and still cannot figure this out, please tell me what i need to put in the code list validation bax, thanks so much |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Validation Lookup Table Help Please
did you include the sheet number as below
=MATCH(sheet2!$A$1,sheet2!$A$2:$A$100) "Kenny" wrote: this does not work, the list validation needs to refernce sheet2 not sheet 1, i get an error with your code "Joel" wrote: You have to use match. If the table starts in row 1 then =MATCH($A$1,$A$2:$A$100) if the table starts in some other row then =MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1 Match will give you the index number into the array. So if you know the row number of the first member (row(A10)) then you simply add the index return by match to the starting row number. "Kenny" wrote: sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is selected it will show sheet2 column A choices, no biggie so far, but based on the list validation choice, I want it to return the value in the corresponding row in sheet2 column b in cell I3 IE: Sheet 2 Column A Column B Hard Drive 2 Monitor 3 keyboard 4 sheet 1 cell I3 list validation shows column a choices, I select hard drive it will put a 2 in cell I3 not Hard Drive???? Also I have looked at contextures code and still cannot figure this out, please tell me what i need to put in the code list validation bax, thanks so much |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
List Validation Lookup Table Help Please
maybe i am not explaining this well enough sorry. I am using a list
validation on the cell in sheet 1. I want to click on the drop down and it show a list inside the drop down that matches column a on sheet 2, when I click on a choice it will actually populate the cell with the corresponind choice in column b from sheet 2. You can not use sheet references in the list validation formula. I have the data range in colum a labled as CodeDescription I have column B labled as Code and both columns together labled as CodeTable.... Is this possible? Also I would like the formula to suppress blank spots that may be contained in a or b offset? Thanks! "Joel" wrote: did you include the sheet number as below =MATCH(sheet2!$A$1,sheet2!$A$2:$A$100) "Kenny" wrote: this does not work, the list validation needs to refernce sheet2 not sheet 1, i get an error with your code "Joel" wrote: You have to use match. If the table starts in row 1 then =MATCH($A$1,$A$2:$A$100) if the table starts in some other row then =MATCH($A$10,$A$2:$A$100)-ROW($A$10)+1 Match will give you the index number into the array. So if you know the row number of the first member (row(A10)) then you simply add the index return by match to the starting row number. "Kenny" wrote: sheet1 cell I3, I want to creat a LIST VALIDATION, when the drop down is selected it will show sheet2 column A choices, no biggie so far, but based on the list validation choice, I want it to return the value in the corresponding row in sheet2 column b in cell I3 IE: Sheet 2 Column A Column B Hard Drive 2 Monitor 3 keyboard 4 sheet 1 cell I3 list validation shows column a choices, I select hard drive it will put a 2 in cell I3 not Hard Drive???? Also I have looked at contextures code and still cannot figure this out, please tell me what i need to put in the code list validation bax, thanks so much |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation Data using Validation Table cell range..... | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
validation list--list depends on the selection of first list | New Users to Excel | |||
lookup? list data into summary table | Excel Worksheet Functions |