+ Reply to Thread
Results 1 to 5 of 5

Dynamic Data Validation based on series in a single cell

  1. #1
    Registered User
    Join Date
    09-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Dynamic Data Validation based on series in a single cell

    I'm new to the forums and have done some searching with no luck, so if I have missed this solution in another post, please forgive the error.

    I have a series of numbers listed in a single cell ("Submitted"), separated by spaces. The series can be any combination of numbers from 1-32 and will be entered by the user. I want this cell to be the baseline of possible values that can be entered in 3 other cells labeled "Approved", "Disapproved" and "Discrepancy".

    The values listed in "Submitted" can only be used once, so "1" should never be entered in "Approved" and "Disapproved" at the same time. Numbers not listed in "Submitted" should not appear in "Approved", "Disapproved" or "Discrepancy."

    Please see attached file for examples.
    Attached Files Attached Files

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Dynamic Data Validation based on series in a single cell

    I'm not quite sure of what you ultimately need, but I came up with this:

    1) Users enter 2-digit numeric text: 01 03 05 etc
    ...So all input cells are formatted as TEXT

    2) I reference a lookup list of values 01 through 32

    For each data set:
    • One cell calculates if any two rows contain the same VALID value
    • Another cell calculates if any INVALID values were used.

    I attached an edited version of your file.

    Is there anything in there that you can use?
    Attached Files Attached Files
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    09-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Dynamic Data Validation based on series in a single cell

    This is close - any chance it can be done without converting the entries to text values?

  4. #4
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Dynamic Data Validation based on series in a single cell

    Possibly, but I suspect the formulas will be horrendous.
    Without using 2-digit values, more rigorous testing functions are necessary to avoid complications...otherwise, checking if the cell contains "1" would match on 1, 10, 11, 21, and 31. I recommend working with Excel in this case...instead of against it.

    If you posted a generic example, perhaps your actual scenario might be easier to accommodate.

  5. #5
    Registered User
    Join Date
    09-28-2009
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    3

    Re: Dynamic Data Validation based on series in a single cell

    I was afraid of that, but had to ask anyway. I will see what I can do to make this solution work. Unfortunately, due to confidentiality reasons, I am unable to post the actual scenario. Many thanks for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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