+ Reply to Thread
Results 1 to 10 of 10

Dynamic data validation list

  1. #1
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2019
    Posts
    93

    Dynamic data validation list

    1. I have named ranges "{sheet}Index"
    2. I have a cell/formula that evaluates to the sheet name, say in A1.
    3. I want a dynamic data validation list. I.e. when I'm on sheet "Color" I want to have a data validation list derived from the name range "ColorIndex".


    My attempt to do this consists of the following:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    This does not work...
    I don't know why and I have no better idea.

    Help.

    Also... what the everliving f. is this SecuriFirewall thing that pops up when I try to post a thread with a bunch of <> symbols? vBulletin already escapes user input.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Dynamic data validation list

    1. INDEX may be a reserved tern in excel (there is a regular function called =INDEX() ) so try picking a different name for the range.

    2. The firewall things that when < and > are used, it could be HTML code, so it blocks it. Try adding a space before and after each 1
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Dynamic data validation list

    G'day,

    I tested your theory Ford and I made 3 named ranges with the word index (eg colourindex, greenindex, pinkindex) in them also place them across 3 tabs and the data validation works fine for me.

    The OP mention that

    2. I have a cell/formula that evaluates to the sheet name, say in A1.
    I'm guessing the issue is the type of value is getting returned in that cell.
    Have I made you happy ??? If yes, please make me happy by pressing the http://www.excelforum.com/images/buttons/reputation-40b.png Add Reputation button in my post.
    Please don't forget to do the same to other contributors of this forum.

    Thanks
    I don't void confusion, I create it

  4. #4
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2019
    Posts
    93

    Re: Dynamic data validation list

    Ah, the pitfalls of not offering a reduced test case.

    I think I have somewhat narrowed the problem.
    See, these named ranges I have - they are dynamically calculated. E.g.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in the data validation box, if I put
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    it works
    but if I put
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    it no longer works
    Nor does this work:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    (This one throws an error: "You may not use reference operators (such as unions, intersections and ranges) or array constants for Data Validation criteria.")

    So, if you're familiar with the concept of indirection, that's what we're essentially playing with here - extracting my range to a variable and trying to eval that variable name dynamically.

    What is amusing/infuriating here is that 0 or 2 levels of indirection do not work, but 1 level of indirection works perfectly.

    Like... what the hell?

    P.S. Speaking of INDIRECT, what is also mildly amusing, but mostly infuriating is that INDIRECT literally functions differently based on the context within which it is used.
    E.g. =MATCH(1, INDIRECT(refAsString)) works one way.
    =MATCH(1, A1)
    A1 =INDIRECT(refAsString)
    Works another.

    Upon which I call absolute BS.

  5. #5
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Dynamic data validation list

    G'day,

    Still not understanding how you want cell A1 to return a value for you, whether its by formula or having a data validation box. I'm guessing you may need another approach to get the required result.

    A workbook is of more use.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Dynamic data validation list

    Pl see attached file.
    H1 is validated in each sheet.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  7. #7
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2019
    Posts
    93

    Re: Dynamic data validation list

    Quote Originally Posted by ratcat View Post
    G'day,

    Still not understanding how you want cell A1 to return a value for you, whether its by formula or having a data validation box. I'm guessing you may need another approach to get the required result.

    A workbook is of more use.

    1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired results are also shown (mock up the results manually).

    3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

    4. Try to avoid using merged cells as they cause lots of problems.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

    Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.
    The next poster, kvsrinivasamurthy, already attached a file.
    It is almost what I need, but not quite.

    Take his sheet, go into the Name Manager and change what aaaIndex refers to from:
    =aaa!$D$1:$D$9
    to
    =aaa!$D$1:INDEX(aaa!$D$1:$D$20, 9)

    Try the validation dropdown in aaa!H1 again.
    Doesn't work, does it?

    How do I make it work, while preserving the dynamic nature of the range AND the validation source?
    Last edited by martix; 11-13-2018 at 12:06 PM.

  8. #8
    Valued Forum Contributor ratcat's Avatar
    Join Date
    03-07-2008
    Location
    Rural NSW, Australia
    MS-Off Ver
    Vista 2007
    Posts
    1,111

    Re: Dynamic data validation list

    Unfortunately you're going to find another way to get a working solution that works for you. There will be solutions like helper columns and/or other formulas to assist the goal your trying to achieve. Without knowing basic layout and operation working of your workbook this is all I can offer you.

    By the way I'm not nerdy enough to explain why it won't work for you or for anyone else for that matter.

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Dynamic data validation list

    Worksheet events with VB code is to be used for dynamic range
    Last edited by kvsrinivasamurthy; 11-15-2018 at 03:47 AM.

  10. #10
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2019
    Posts
    93

    Re: Dynamic data validation list

    Quote Originally Posted by ratcat View Post
    Unfortunately you're going to find another way to get a working solution that works for you. There will be solutions like helper columns and/or other formulas to assist the goal your trying to achieve. Without knowing basic layout and operation working of your workbook this is all I can offer you.

    By the way I'm not nerdy enough to explain why it won't work for you or for anyone else for that matter.
    I have offered what you asked for. Or, well, kvsrinivasamurthy created the workbook and I explained the single necessary change. Looking at two posts, rather than having everything served on a single silver platter should not be a barrier to helping.

    But yes, I eventually arrived at the solution of using a helper column by myself.

    Here is the tally:
    0 levels of indirection
    Validation source: =aaa!$D$1:INDEX(aaa!$D$1:$D$20, 9)
    × Doesn't work.

    1 level of indirection
    definedName: =aaa!$D$1:INDEX(aaa!$D$1:$D$20, 9)
    Validation source: =definedName
    Works

    2 levels of indirection
    A1 ="definedName"
    definedName: =aaa!$D$1:INDEX(aaa!$D$1:$D$20, 9)
    Validation source: =INDIRECT(A1)
    × Doesn't work.

    3 levels of indirection
    B1 ="aaa!$D$1", B2="aaa!$D$1:$D$20"
    Helper!A1:A20 {=INDIRECT(B1):INDEX(INDIRECT(B2), 10)}
    definedName: =Helper!A1:A20
    Validation source: =definedName
    Works

+ 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 Data Validation List or ComboBox
    By mjo1983 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-06-2018, 12:56 PM
  2. Dynamic dependent Validation List based in multiple tables and formulas
    By se7en___ in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-30-2018, 04:47 PM
  3. [SOLVED] VBA Excel adding dynamic validation list to multiple cells
    By Lokesh3_14 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-22-2016, 11:20 AM
  4. [SOLVED] Dynamic list connect to validation data
    By pedjvak in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-09-2013, 04:51 AM
  5. [SOLVED] Dynamic Conditional Data Validation List
    By rackman70 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-13-2012, 11:14 AM
  6. Replies: 0
    Last Post: 02-13-2012, 07:18 AM
  7. Data Validation with Dynamic List using VBA
    By nivassrii in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2011, 01:13 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