| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
validate lists
How can I validate more than two lists?
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
Hi,
What do you mean by 'validate a list'? -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: How can I validate more than two lists? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
I'm talking about data validation
"sebastienm" wrote: Hi, What do you mean by 'validate a list'? -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: How can I validate more than two lists? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
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 "Imaginator" wrote: How can I validate more than two lists? |
|
#5
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 |
|
#6
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
|
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
|
|
#10
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 |
|
#11
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
validate lists
Here is a recap after our email exchange:
Description and Problem ------------------------ - data enties are in column F, H, J rows 4 to 16 - in F, the user pick from a Data Validation List, List1 - in H, user picks from a Data Validation List, List2. This list depends on the value in F on the same row. - in J, user picks from a Data Validation List, List3. This list depends on the value in J on the same row. -- How to create these dependent validation lists Sheets ------- The above entries are in sheet Data Entry and add a second sheet, Lists, to store and organize the lists Lists ------ - List1 is in sheet ListsRevenue, Administrative,Warranty, and Sick or Vacation Time - List2 for Revenue is: On Site Commissioning, Site Reports, Pre-Site Work, Post-Site Work, Training, Telephone & Email Support, VP&D Support, RMA request, Quotes, Upgrades - List2 for Administrative is : Expense Reports, Telephone & Email , Field Service, Reports, Infotrac Reports, Travel Arrangements, Meetings / Conference Calls - List 2 for Warranty is : Customer Support, Parts - List 2 for Sick or Vacation Time is : Sick Time, Vacation Time -List 3 ... same idea Organize the Lists sheet ------------------------ 1 - Add a sheet called Lists - Put list one in B4:B7 - For List2, copy/paste the Transpose of List1 into B13:E13 -- header - bellow each header enter the corresponding List2: - D13 is 'Warranty' form List1 - In D14:D15, enter Customer Support, Parts -- list2 for Warranty - same idea for List 3 - copy each items from all lists of List 2 in one row B28:U28 - bellow each header, enter the correwsponding List3 items 2 Named ranges - List1: First list is easy, I just set the named range List1 to $B$4:$B$7 - List2: Second list List2 is a dependent list: it depends on the selection made from List1. 1. Transpose all the values from List1 on the top row and enter lists underneath filed name. 2. Set a first named range called List2r (with a small 'r' ifor 'row' as the top row ie header) ie cells $B$13:$E$13. This is where we search the top row to determine which list to use based on choice from List1 3. Set a second named range called List2d (with a small 'd' ifor 'data' as lists themselves, ie $B$14:$E$23 (we'll determine later the length of each list) 4. Add the final Named range List2: =OFFSET(List2d,0,MATCH( 'Data Entry'!$F4, List2r, 0) - 1,COUNTA(OFFSET(List2d, 0, MATCH('Data Entry'!$F4,List2r,0) -1, ROWS(List2d), 1)),1) 5. on sheet 'Data Entry, select H4:Hxxx and apply the data validation: List =List2 Here we could have done everything in 1 single formula without using intermediate List2r and List2d but it makes everything painfull. Also, as you can see, each cell of H has validation on List2... no need of separate list for each cell of H. - List3: For List3, just follow exactly the same process as for List 2 with: List3r : =$B$28:$U$28 List3d : =$B$29:$U$33 List3 : replace List2d by List3d, List2r by List3r, 'Data Entry'!$F4 by 'Data Entry'!$H4 since it depends of value in $H =OFFSET(List3d,0,MATCH( 'Data Entry'!$H4, List3r, 0) - 1,COUNTA(OFFSET(List3d, 0, MATCH('Data Entry'!$H4,List3r,0) -1, ROWS(List3d), 1)),1) If you wanted to add more dependent lists follow the process for list 2 the same way you did it for List3 Issue ------ Say F4, H4, J4 have been entered Now the user modifies F4. -- H4 and J4 stay the same as they were before the change ie they are part of old List2 and List3 but not of the new List2 and List3 To prevent this issue, add code in the sheet _Change event to clear H4 and J4 after F4 has been modified. Same idea: clear J4 when H4 is modified. Put the macro below into the Data Entry sheet code module. Just 2 lines of code need to be changed to adapt to a different set of dependencies (section 'CHANGE HERE'): '------------------------------------------------------ Private Sub Worksheet_Change(ByVal Target As Range) Dim DepCols 'array of dependent columns Dim RgDepEntries As Range 'range of columns Dim RgDepTarget As Range 'range intersect DepEntries and Target Dim rg As Range Dim str As String Dim ub As Long, pos As Long, i As Long 'CHANGE HERE --------------------------- DepCols = Array("F", "H", "J") 'ordered by dependence Set RgDepEntries = Range("F4:F16,H4:H16,J4:J16") 'correspomding range with row numbers '--------------------------------------- Set RgDepTarget = Application.Intersect(Target, RgDepEntries) If RgDepTarget Is Nothing Then Exit Sub 'Exit if no entry in that range 'from now on, assumes RgDepTarget as rectangular area 'get letter of 1st column being changed Set rg = RgDepTarget.Cells(1) str = Left(rg.Address(False, False), IIf(rg.Column 26, 2, 1)) 'look for next columns in dependency and clear them pos = WorksheetFunction.Match(str, DepCols, 0) ub = UBound(DepCols) If pos - 1 < ub Then 'if not last column in dependecy then clear Application.EnableEvents = False For i = pos To ub Set rg = Application.Intersect(Range(DepCols(i) & ":" & DepCols(i)), RgDepEntries, RgDepTarget.EntireRow) rg.ClearContents Next Application.EnableEvents = True End If '-find first cell column letter 'first cell column letter, used as reference for top dependency, the rest (right side) is cleared End Sub '-------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com "Imaginator" wrote: 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) | |||
Linear Mode
