+ Reply to Thread
Results 1 to 7 of 7

Dynamic/Offset Data Validation

  1. #1
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    189

    Dynamic/Offset Data Validation

    Hi all,

    I've got a repitition problem. I have multiple data ranges in one worksheet where I need to create a named range for each. Example:

    -------A
    1----Name
    2-----a
    3-----b
    4-----c
    5-----
    6-----

    This continues for quite a lot of columns across the worksheet. I know that to do this once I would highlight cells A1:A6 and create a name on the formula tab, create from selection, top row. I also know that I could just highlight cells A1:ZZ6 and create from selection and it would do that too.

    My other problem is that not every column will contain 6 values so will be blank which I wouldn't want appearing in Data Validation lists. And again, I know that if I was only doing this once I would use the name manager and edit the range using the offset formula. But, as you can imangine I don't want to be doing this for hundred of named ranges.

    Does anybody know a way how I can combine these methods but also to do it automatically rather than having to repeat this process for every column.

    Thanks in advance,

    Jason

  2. #2
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    189

    Re: Dynamic/Offset Data Validation

    Or alternatively...

    Is it possible to have data validation lists where insted of defining a name or a range of cells to use as the source, use a formula that would search a seperate worksheet for a cell containing any given unique phrase and use the 6 non blank cells below that cell as the data validation source. If that is possible, I think it would be the perfect solution for me. I've tried doing google searches around this but that only led me to dynamic named ranges, the offset formula for blanks and using table as source ranges.

  3. #3
    Forum Expert
    Join Date
    03-28-2014
    Location
    Hyderabad,India
    MS-Off Ver
    Excel 2013
    Posts
    1,827

    Re: Dynamic/Offset Data Validation

    Hi,

    Provide sample workbook with out any confidential data .

    Punnam

  4. #4
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Dynamic/Offset Data Validation

    This code will create a named range for every column in Sheet1.

    The ranges are dynamic, so as values are added / deleted, the named range will continue to reflect the list of values.

    Please Login or Register  to view this content.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  5. #5
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    189

    Re: Dynamic/Offset Data Validation

    Quote Originally Posted by Punnam View Post
    Hi,

    Provide sample workbook with out any confidential data .

    Punnam
    Sample workbook attached.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    07-17-2013
    Location
    Birmingham, England
    MS-Off Ver
    Excel 2013
    Posts
    189

    Re: Dynamic/Offset Data Validation

    Quote Originally Posted by Olly View Post
    This code will create a named range for every column in Sheet1.

    The ranges are dynamic, so as values are added / deleted, the named range will continue to reflect the list of values.

    Please Login or Register  to view this content.
    Hi Olly,

    Could I insert this code to have it name all the ranges, but then delete the code and the names remain? The reason I ask is because this document is to be used on an iPad Air with the office Excel app which doesn't seem to like Macro Enabled Workbooks and just crashes.

    Also, I imagine this is going to change the code entirely:

    There is more than one range of data that I would need to be a named range within any one column. I presume your code would just roll all these up into one named range? Bit hard to explain but if you have a look at the sample file I've uploaded you'll see what I mean.

    Many thanks,

    Jason

  7. #7
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,249

    Re: Dynamic/Offset Data Validation

    Okay - based on your sample file, try this:
    Please Login or Register  to view this content.
    Of course it's possible to run the code once, which will create the dynamic named ranges, then save as a non-macro enabled workbook - the defined named ranges will still exist!

+ 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. [SOLVED] Data Validation + OFFSET + COUNTIFS
    By irmaosver in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-06-2014, 03:35 PM
  2. Data validation, IF, OFFSET & COUNTA
    By alfgrey in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-24-2013, 08:27 PM
  3. Dynamic Offset Summarize Pivot Data
    By matt4003 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-06-2012, 06:07 AM
  4. Combining autotext, multiple value validation and dynamic data offset?
    By Chatis in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-21-2009, 09:38 PM
  5. Dynamic Range, Data Validation and Address, Match and Offset Funct
    By rudawg in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2006, 11:25 PM

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