| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate lists
Could you please be more specific.
I assume you are trying to have data validation as follow: - The data-entry cells value must be found in the range Data So far, no problem, nothing different than normal. You would use a regular List type of data validation. - The issue is that Data is made of 3 non-continuous ranges: Data1, Data2, and Data3 In this case, you could use a function that checks is the value is in one (at least) of these 3 ranges. To use a function, you would the Custom type of data validation instead of the List type: Assuming the data entry cell is A1 =NOT(AND(ISERROR(MATCH(B2,data1,0)),ISERROR(MATCH( B2,data2,0)),ISERROR(MATCH(B2,data3,0)))) would that work for you? -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: I really need an answer if you can provide one "sebastienm" wrote: Hi, What do you mean by 'validate a list'? -- Regards, Sébastien <http://www.ondemandanalysis.com |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
The issue is that I have one cell validated list that contains "Revenue - Administration - Warranty - Sick or Vacation Pay" the cell next to it validates what function the field engineer did i.e. "system commissioning for Revenue or emails for Administration" the next cell should call out to a time sheet that they fill in and reflects back to let's say " Revenue- System Config. If this is confusing myabe I can send you the .xls sheet to look at? "sebastienm" wrote: Could you please be more specific. I assume you are trying to have data validation as follow: - The data-entry cells value must be found in the range Data So far, no problem, nothing different than normal. You would use a regular List type of data validation. - The issue is that Data is made of 3 non-continuous ranges: Data1, Data2, and Data3 In this case, you could use a function that checks is the value is in one (at least) of these 3 ranges. To use a function, you would the Custom type of data validation instead of the List type: Assuming the data entry cell is A1: =NOT(AND(ISERROR(MATCH(B2,data1,0)),ISERROR(MATCH( B2,data2,0)),ISERROR(MATCH(B2,data3,0)))) would that work for you? -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: I really need an answer if you can provide one "sebastienm" wrote: Hi, What do you mean by 'validate a list'? -- Regards, Sébastien <http://www.ondemandanalysis.com |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
|
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
|
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
Yes - I want the Total Time Spent cell to reference back to the data validation drop down list that contains - Admin-Revenue- Warranty-Sick or Vacation etc. and have that cell reference the Time Allocation table. "sebastienm" wrote: I got it, yes. I think I get it know; you are tring to implement Dependent Lists for Data Validation, isn't it? I 'll work on it once i get home tonight. -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: Did you get my email? "sebastienm" wrote: yes you can email it to remove 'dom' right after the @ in the email address. -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: The issue is that I have one cell validated list that contains "Revenue - Administration - Warranty - Sick or Vacation Pay" the cell next to it validates what function the field engineer did i.e. "system commissioning for Revenue or emails for Administration" the next cell should call out to a time sheet that they fill in and reflects back to let's say " Revenue- System Config. If this is confusing myabe I can send you the .xls sheet to look at? "sebastienm" wrote: Could you please be more specific. I assume you are trying to have data validation as follow: - The data-entry cells value must be found in the range Data So far, no problem, nothing different than normal. You would use a regular List type of data validation. - The issue is that Data is made of 3 non-continuous ranges: Data1, Data2, and Data3 In this case, you could use a function that checks is the value is in one (at least) of these 3 ranges. To use a function, you would the Custom type of data validation instead of the List type: Assuming the data entry cell is A1: =NOT(AND(ISERROR(MATCH(B2,data1,0)),ISERROR(MATCH( B2,data2,0)),ISERROR(MATCH(B2,data3,0)))) would that work for you? -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: I really need an answer if you can provide one "sebastienm" wrote: Hi, What do you mean by 'validate a list'? -- Regards, Sébastien <http://www.ondemandanalysis.com |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
Any Luck?
"Imaginator" wrote: Yes - I want the Total Time Spent cell to reference back to the data validation drop down list that contains - Admin-Revenue- Warranty-Sick or Vacation etc. and have that cell reference the Time Allocation table. "sebastienm" wrote: I got it, yes. I think I get it know; you are tring to implement Dependent Lists for Data Validation, isn't it? I 'll work on it once i get home tonight. -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: Did you get my email? "sebastienm" wrote: yes you can email it to remove 'dom' right after the @ in the email address. -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: The issue is that I have one cell validated list that contains "Revenue - Administration - Warranty - Sick or Vacation Pay" the cell next to it validates what function the field engineer did i.e. "system commissioning for Revenue or emails for Administration" the next cell should call out to a time sheet that they fill in and reflects back to let's say " Revenue- System Config. If this is confusing myabe I can send you the .xls sheet to look at? "sebastienm" wrote: Could you please be more specific. I assume you are trying to have data validation as follow: - The data-entry cells value must be found in the range Data So far, no problem, nothing different than normal. You would use a regular List type of data validation. - The issue is that Data is made of 3 non-continuous ranges: Data1, Data2, and Data3 In this case, you could use a function that checks is the value is in one (at least) of these 3 ranges. To use a function, you would the Custom type of data validation instead of the List type: Assuming the data entry cell is A1: =NOT(AND(ISERROR(MATCH(B2,data1,0)),ISERROR(MATCH( B2,data2,0)),ISERROR(MATCH(B2,data3,0)))) would that work for you? -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: I really need an answer if you can provide one "sebastienm" wrote: Hi, What do you mean by 'validate a list'? -- Regards, Sébastien <http://www.ondemandanalysis.com |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Drop down lists from multiple source lists | Excel Worksheet Functions | |||
| validate | Excel Discussion (Misc queries) | |||
| LISTS- adding info without repeat to other lists | Excel Discussion (Misc queries) | |||
| Multiple lists with repeated values for dependet drop down lists | Excel Worksheet Functions | |||
| How do I validate data using different lists based on the data in. | Excel Discussion (Misc queries) | |||
Hybrid Mode
