+ Reply to Thread
Results 1 to 10 of 10

Data Validation Indirect Function linked to Dynamic Named Range

  1. #1
    Registered User
    Join Date
    07-13-2009
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    81

    Data Validation Indirect Function linked to Dynamic Named Range

    I have a number of dynamic named ranges which I have created using the ofest function eg =OFFSET($B$6,0,0,COUNTA($B$6:$B$106),1)
    I'd like to be able to make an indirect function within data validation to that range (the name of the range is in B5)
    When I try and do this it wont allow me to but it will if i 'fix' the range so that its is B6:B106

    Any ideas?

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,562

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    Can you post your sheet?
    Glenn



  3. #3
    Registered User
    Join Date
    07-13-2009
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    Sorry - My organisation doesnt allow this and Im working through one of their laptops

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,562

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    What's the name of the range you're using

  5. #5
    Registered User
    Join Date
    07-13-2009
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    just 'Months'

  6. #6
    Registered User
    Join Date
    07-13-2009
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    Ive spoken to the lovely people in IT - Here is the file
    Attached Files Attached Files

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2302
    Posts
    38,562

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    I guess I'm having a stoopid day... Exactly what are you trying to achieve?

  8. #8
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    Here is a file with examples of various kinds of dynamic Data Validation lists. Two of them use the OFFSET function and one uses a table.

    Hope this helps.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  9. #9
    Registered User
    Join Date
    07-13-2009
    Location
    Bristol, England
    MS-Off Ver
    Excel 2010
    Posts
    81

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    Thanks newdoverman
    Glen. What I'm trying to do is use the named range 'Month' (and others, all of which are dynamic) as part of a formula in another worksheet so from a list a specific entry by the user they will get a validation list relevant to their entry. Hope that makes sense

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Data Validation Indirect Function linked to Dynamic Named Range

    What is the formula that you are trying too write using the names?

    One thing that you can try is to write the formula using actual cell references (like a normal formula) then substitute the correct names in the formula for the cell ranges concerned.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dynamic Named Ranged & Indirect Data Validation
    By Voluntondile in forum Excel General
    Replies: 4
    Last Post: 10-01-2014, 08:12 PM
  2. Dynamic Named Ranges and Indirect Data validation INCOMPATIBLE?
    By css0911 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-08-2013, 06:17 AM
  3. Replies: 14
    Last Post: 10-02-2013, 07:39 AM
  4. Replies: 2
    Last Post: 06-03-2013, 08:26 AM
  5. Using INDIRECT to put a Dynamic Named Range in a chart Data Source
    By mgaworecki in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 10-01-2012, 07:35 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1