Announcement

Collapse
No announcement yet.

Multiple Named Ranges In Data Validation List

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Multiple Named Ranges In Data Validation List

    hello.. is it possible to refer to more then one named range in my validated list's source field? If not, how can i make more than 1 named range to be the source of my validated list?
    thank you very much..
    Last edited by belthazar; February 26th, 2008, 13:13. Reason: Wrong term used. (named list instead of named range)

  • #2
    Re: Refer to more than one named list

    To quote Excel,

    You may not use unions, intersections, or array constants for Data Validation Criteria.

    The list source must be a delimited list, or a reference to a single row or column.
    Entia non sunt multiplicanda sine necessitate.

    Comment


    • #3
      Re: Refer to more than one named list

      If you mean Data Validation, you cannot without combining the lists into 1, name it and use it in the List source.

      Comment


      • #4
        Re: Refer to more than one named list

        Originally posted by Dave Hawley
        If you mean Data Validation, you cannot without combining the lists into 1, name it and use it in the List source.
        how exactly can i combine it? i have an idea though

        - get the values from the named range
        - create new list based from the values retrieved from the named lists
        - set the source of the validated list cell to the new combined list.

        the problem is i don't know how exactly i'll do it.

        Comment


        • #5
          Re: Multiple Named Ranges In Data Validation List

          how exactly can i combine it?
          Copy & paste would be my 1st port-of-call

          Comment


          • #6
            Re: Multiple Named Ranges In Data Validation List

            Originally posted by Dave Hawley
            Copy & paste would be my 1st port-of-call
            i'm sorry.. i forgot to mention that the named range is dynamic. so i really need to use the Named range's Name to refer to it, not just manually creating the combined list but by using VBA get the reference of the named ranged and combine it and then set the validated range's source to the new combined named dynamic range.

            Comment


            • #7
              Re: Multiple Named Ranges In Data Validation List

              =A1 and copy down to combine the dynamic ranges and create another dynamic range based on COUNTIF($A$1:$A$1000,"<>0")

              Comment


              • #8
                Re: Multiple Named Ranges In Data Validation List

                im sorry if i don't understand the solution but i just want to show you what are the sources of my named range..

                This are my dynamic Named Range:

                ARN:
                source: =OFFSET('sheet1'!$E$16,0,0,COUNTA('sheet1'!$E:$E),1)

                DRN:
                source:
                =OFFSET('sheet2!$E$16,0,0,COUNTA('sheet2'!$E:$E),1)

                NGRN:
                source:
                =OFFSET('sheet3'!$E$16,0,0,COUNTA('sheet3'!$E:$E),1)

                as you can see, they are in different sheets...

                i want to create a RN named range where the source is ARN+DRN+NGRN and use it in another sheet.

                is this possible?

                Comment


                • #9
                  Re: Multiple Named Ranges In Data Validation List

                  Right click on the Excel icon, top left next to File, choose View Code and use/modify;
                  Code:
                  Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
                      With Sheet4 ' ALL are CodeNames (follow 1st link) of Sheets
                              .Columns(1).Clear' Clear MyBigList ready for copy/paste
                              Sheet1.Range("ARN").Copy .Range("A1") 'Copy 1st dynamic range and paste to A1 of Sheet4
                              Sheet2.Range("DRN").Copy .Cells(.Rows.Count, "A").End(xlUp)(2, 1) 'Copy 2nd dynamic range and paste to 1 below last used row of Sheet4
                  
                              Sheet3.Range("NGRN").Copy .Cells(.Rows.Count, "A").End(xlUp)(2, 1) 'Copy 3rd dynamic range and paste to 1 below last used row of Sheet4
                  
                              .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Name = "MyBigList" 'Name the new dynamic range
                      End With
                  End Sub

                  Comment


                  • #10
                    Re: Multiple Named Ranges In Data Validation List

                    Originally posted by Dave Hawley
                    Right click on the Excel icon, top left next to File, choose View Code and use/modify;
                    Code:
                    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
                        With Sheet4 ' CodeNames
                                .Columns(1).Clear
                                Sheet1.Range("ARN").Copy .Range("A1")
                                Sheet2.Range("DRN").Copy .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
                                Sheet3.Range("NGRN").Copy .Cells(.Rows.Count, "A").End(xlUp)(2, 1)
                                .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Name = "MyBigList"
                        End With
                    End Sub
                    uhm, can you run me thorugh the code? i really am not so familiar with vba. i'd really appreciate it. thank you so much.

                    Comment


                    • #11
                      Re: Multiple Named Ranges In Data Validation List

                      Just added code comments.

                      Comment


                      • #12
                        Re: Multiple Named Ranges In Data Validation List

                        does this code

                        Code:
                        With Sheet4 ' CodeNames
                                .Columns(1).Clear
                        1. refer to the first column of sheet4 which is the A column?
                        1.1 if yes, will it clear all values in column A? from A1->A65536?

                        2.what makes .Columns(1).Clear refer to MyBigList?

                        Comment


                        • #13
                          Re: Multiple Named Ranges In Data Validation List

                          Yes and yes.

                          2. MyBigList is a dynically created named range in Column A of Sheet4

                          Comment


                          • #14
                            Re: Multiple Named Ranges In Data Validation List

                            I see.. I made the code work but it only displayed the concatenated list values to cell A1. i want MyBigList to be just a source to a List. ( =MyBigList sumthing like this) for example i want A1 to be a dropdown list containing MyBigList.

                            Comment


                            • #15
                              Re: Multiple Named Ranges In Data Validation List

                              Select A1 Go Data>Validation and Enter =MyBigList in the list source for Data Validation

                              Comment

                              Working...
                              X