Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 20

Thread: Multiple Named Ranges In Data Validation List

  1. #1
    Join Date
    26th February 2008
    Posts
    9

    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 at 12:13. Reason: Wrong term used. (named list instead of named range)

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,322

    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.
    MS MVP - Excel

  3. #3
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    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.

  4. #4
    Join Date
    26th February 2008
    Posts
    9

    Re: Refer to more than one named list

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    Re: Multiple Named Ranges In Data Validation List

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

  6. #6
    Join Date
    26th February 2008
    Posts
    9

    Re: Multiple Named Ranges In Data Validation List

    Quote 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.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    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")

  8. #8
    Join Date
    26th February 2008
    Posts
    9

    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?

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,698

    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;
    VB:
    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 
    
    

  10. #10
    Join Date
    26th February 2008
    Posts
    9

    Re: Multiple Named Ranges In Data Validation List

    Quote Originally Posted by Dave Hawley
    Right click on the Excel icon, top left next to File, choose View Code and use/modify;
    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 4
    Last Post: November 11th, 2008, 17:13
  2. Data Validation With Two Named Ranges
    By keekooceeaou in forum EXCEL HELP
    Replies: 7
    Last Post: May 8th, 2007, 20:13
  3. Multiple Column Data Validation List
    By rdblatch in forum EXCEL HELP
    Replies: 15
    Last Post: February 4th, 2006, 02:37
  4. DATA VALIDATION (from 2 non-adjacent list-ranges)
    By Michael Avidan in forum EXCEL HELP
    Replies: 3
    Last Post: January 31st, 2006, 16:33
  5. Data Validation: Using named ranges
    By axcell in forum EXCEL HELP
    Replies: 4
    Last Post: November 24th, 2003, 08:48

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno