+ Reply to Thread
Results 1 to 5 of 5

Data Validation rule based on the result of UDF

  1. #1
    Registered User
    Join Date
    08-15-2014
    Location
    St.Petersburg, Russia
    MS-Off Ver
    MSO Excel 2010
    Posts
    20

    Data Validation rule based on the result of UDF

    Hi All,

    Can I use in cells data validation results of user-defined function?

    I have an UDF that gets Custom Fill List contents by custom fill list index. Let it named FillList, and a result is an array of strings
    Then I trying to enter Data Validation source as =FillList(1). It do not works.
    Ok, I create name, for example, "first_list", then in name source I enter =FillList(1). Name created.

    Then, when I try to enter =first_list as source for data validation, it fails (of course)

    Is there are any other ways to use an array was got as result of UDF in Data Validation rules?
    Or here may be other way to use custom fill list as data validation rule?

    Thanks!
    --
    Maxim

  2. #2
    Valued Forum Contributor
    Join Date
    11-22-2014
    Location
    Arizona, USA
    MS-Off Ver
    Office 365
    Posts
    973

    Re: Data Validation rule based on the result of UDF

    =Countifs(CELL,FillList(1))>0
    or
    =Countifs(CELL,First_List)>0

    CELL should be the first cell in relation to your selected area to be compared against the list for highlighting -

    Cheers
    -If you think you are done, Start over - ELeGault

  3. #3
    Registered User
    Join Date
    08-15-2014
    Location
    St.Petersburg, Russia
    MS-Off Ver
    MSO Excel 2010
    Posts
    20

    Re: Data Validation rule based on the result of UDF

    Thank you, ELeGault!
    Tried both variants as source to data validation (List and Other). With UDF in formula it generates error, with name in formula it doesn't works (I still can enter any value in cell)
    I think you solutions is good for conditional formatting, but my aim is to use "custom fill list" as source of Data Validation rule, to avoid users enter other values than in this list. Not to create named range or use directly inputed array of values, but use saved on computer custom fill list.

    And then this goes to question - how to apply an array of values (not cells), generated by function, as a source to DV.
    Any suggestions?

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Data Validation rule based on the result of UDF

    No you cant use array resulting function in Validation List


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  5. #5
    Registered User
    Join Date
    08-15-2014
    Location
    St.Petersburg, Russia
    MS-Off Ver
    MSO Excel 2010
    Posts
    20

    Re: Data Validation rule based on the result of UDF

    Quote Originally Posted by :) Sixthsense :) View Post
    No you cant use array resulting function in Validation List
    It's a pity... Thanks anyway!

+ 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. Data Validation List based on formula result
    By dancing-shadow in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-25-2015, 11:49 AM
  2. [SOLVED] Drop Down Data Validation - Display a result based on multiple conditions
    By carlmf1987 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2013, 01:50 PM
  3. Replies: 5
    Last Post: 09-23-2013, 05:06 PM
  4. Adding multiple cells based on the result of a data validation cell
    By ronrona96 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 01-20-2013, 12:19 AM
  5. data validation-setup a validation rule
    By de049 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-07-2008, 08:17 AM

Tags for this Thread

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