none
how to do a data validation (List) from multiple columns (in different sheets) on a column in excel vsto?

    Question

  • I have 2 lists in sheet1 and sheet 2.

    list 1 has values :

    a

    b

    c

    d

    e

    f

    list 2 contains values :

    g

    h

    i

    j

    In the dropdown for list 3 I want the values as  :

    a

    b

    c

    d

    e

    f

    g

    h

    i

    j

    Currently I can get the values from list 1 using data validation in c#. But I am not able to fetch the values from list1 and list2 and populate them in the drop down (using data validation in c#).

    My code to get lookup values is as follows :

    Range validatingCellsRange = someCell.EntireColumn;
    
    lookupValues = "=" + lookupWorkSheetName + "!$" + "A" + "$2:$" + "A" + "$1048576";
    
    validatingCellsRange.Validation.Delete();
                                validatingCellsRange.Validation.Add(XlDVType.xlValidateList,
                                       XlDVAlertStyle.xlValidAlertInformation,
                                       XlFormatConditionOperator.xlBetween, lookupValues, Type.Missing);
                                validatingCellsRange.Validation.IgnoreBlank = true;
                                validatingCellsRange.Validation.InCellDropdown = true;

    Can someone please help me with this ?

    snapshot explaining problem


    Shoeb Sayyed ( On the learning curve )


    • Edited by Shoeb Sayyed Tuesday, September 25, 2012 11:44 AM spelling mistake
    Tuesday, September 25, 2012 11:43 AM

Answers

  • The ranges are in separate sheet and non-contagious, you can not directly use with Formula1 although you can specify Range address or comma separated string.

    So in your code you need to get the values and then apply.In VB join function accepts an array and output is a delimited string with the elements of array....

    Hope you can interpret below....

    ActiveCell.Validation.Add Type:=xlValidateList, _
        Formula1:=Join(Application.Transpose(Sheet1.Range("a1:a6").Value), ",") & "," & _
        Join(Application.Transpose(Sheet2.Range("a1:a4").Value), ",")


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Shoeb Sayyed Wednesday, September 26, 2012 4:49 AM
    Tuesday, September 25, 2012 3:12 PM
    Answerer

All replies

  • The validation list must be in a separate range. You'll have to combine List 1 and List 2 and place those values somewhere on the same sheet or on a separate sheet. Then use that new range as the source for the 3rd validation list.
    • Marked as answer by Shoeb Sayyed Wednesday, September 26, 2012 4:28 AM
    • Unmarked as answer by Shoeb Sayyed Wednesday, September 26, 2012 4:49 AM
    Tuesday, September 25, 2012 3:08 PM
  • The ranges are in separate sheet and non-contagious, you can not directly use with Formula1 although you can specify Range address or comma separated string.

    So in your code you need to get the values and then apply.In VB join function accepts an array and output is a delimited string with the elements of array....

    Hope you can interpret below....

    ActiveCell.Validation.Add Type:=xlValidateList, _
        Formula1:=Join(Application.Transpose(Sheet1.Range("a1:a6").Value), ",") & "," & _
        Join(Application.Transpose(Sheet2.Range("a1:a4").Value), ",")


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    • Marked as answer by Shoeb Sayyed Wednesday, September 26, 2012 4:49 AM
    Tuesday, September 25, 2012 3:12 PM
    Answerer
  • thanks for replying.

    I have a restriction. I can't use VBA over here. I need to get this done using c# and Microsoft.Office.Interop libraries


    Shoeb Sayyed ( On the learning curve )

    Wednesday, September 26, 2012 4:49 AM
  • I am not in C#.But Excel Interop is same for both the platform.

    Only Join Function is VB function.Hope some equivalent function is there which will accept array....

    Hope you can interpret in C#....


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.


    Wednesday, September 26, 2012 5:57 AM
    Answerer