+ Reply to Thread
Results 1 to 13 of 13

Simple Data Validation Issue

  1. #1
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Simple Data Validation Issue

    Hi All!

    I have been trying to figure this out for almost the past month and have decided to again reach out to you guys with higher excel learning.

    If you see the attached excel file starting with columns N and to the right, I have 2 different ways I am experimenting with data validation. The second way (row 3) I am having a bit of an issue trying to wrap my mind around. In Q4 I have set up the data validation to show a list of known concepts and made it so that a new concept can be typed in if need be. If a new concept is typed into Q4 I want the data validation in Z4 to reference the "other" concept in column F.

    I have tried using if, iferror and iserror statements to achieve this. I feel as though I am close to what I am trying to accomplish and I am pretty sure this is still an "Excel General" Question.

    Any and all help/pointers would be appreciated.

    Thanks in advance!
    Attached Files Attached Files

  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 2403
    Posts
    43,891

    Re: Simple Data Validation Issue

    I have looked at this a couple of times and simply don't have a clue what you want. Do you mean Q4 and Z4? Open your POSTED sheet and check.

    the cells in Q don't seem to do anything. The cells in Z are blank....

    What answer do you expect to see for a couple of examples?
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Re: Simple Data Validation Issue

    Quote Originally Posted by Glenn Kennedy View Post
    I have looked at this a couple of times and simply don't have a clue what you want. Do you mean Q4 and Z4? Open your POSTED sheet and check.

    the cells in Q don't seem to do anything. The cells in Z are blank....

    What answer do you expect to see for a couple of examples?
    Oi! you are correct, I apologize. I took out columns in the spreadsheet without updating the references in my post. The cells in question are highlighted in yellow, O3 and Q3.

    Here is what I am trying to accomplish: if a concept that does not exist in column C I want the user to be able to type that new concept in O3 (which can already be accomplished). However, if a new concept is typed in I want the data validation to pretend that the new typed in concept is "Other" so it will produce the options in G40:G47.

    As of now it can produce families based off of known concepts, but I have a hard time getting it to reference the "Other" concept if a new concept is typed in.

    And, I apologize but see this attached spreadsheet as this will work.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Re: Simple Data Validation Issue

    Does anyone have any ideas/suggestions?

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

    Re: Simple Data Validation Issue

    To repeat myself: What answer do you expect to see for a couple of examples?

  6. #6
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Re: Simple Data Validation Issue

    Quote Originally Posted by Glenn Kennedy View Post
    To repeat myself: What answer do you expect to see for a couple of examples?
    If a new concept is typed in I want the data validation to return the families associated with "Other" so the values to be selected would be what you see in G40:G47 (Family1, Family2, Family3, Family4, Family5, Family6, Family7 and Undefined).

  7. #7
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Re: Simple Data Validation Issue

    Bump, if that's a thing here

  8. #8
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Re: Simple Data Validation Issue

    Anyone wanna take a crack at it?

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

    Re: Simple Data Validation Issue

    There is a bump... There have been a few glitches here. I didn't get notified about any of your updates... until this last one. I'm still not sure if this is what you want, but take a look.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Re: Simple Data Validation Issue

    Quote Originally Posted by Glenn Kennedy View Post
    There is a bump... There have been a few glitches here. I didn't get notified about any of your updates... until this last one. I'm still not sure if this is what you want, but take a look.
    I apologize, I thought I replied but apparently I didn't.

    But, this is soo close! Is there any way to put that formula (which produces the results I need) into data validation so that instead of putting each family into its own cell it instead allows the user to select them from a list?

    Thanks for your help so far!

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

    Re: Simple Data Validation Issue

    I think that you'll have to physically generate the list SOMEWHERE. I put it on a new sheet "Hidden", which can be ... hidden.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Re: Simple Data Validation Issue

    Quote Originally Posted by Glenn Kennedy View Post
    I think that you'll have to physically generate the list SOMEWHERE. I put it on a new sheet "Hidden", which can be ... hidden.
    Sorry for the late reply. If you look at the formula I put in cell Q2, the drop down list is generated by putting a formula in data validation. Apparently, I can't get iferror or ifna statements to work in data validation. It's tough **** :P

  13. #13
    Registered User
    Join Date
    08-17-2016
    Location
    New York
    MS-Off Ver
    2016
    Posts
    20

    Question Re: Simple Data Validation Issue

    So I have an update, although I didn't quite solve the problem I like to tell myself that it was a step in the right direction so I can sleep better tonight.

    If you see the attached file, I put the data validation formula found in cell Q6 into the data validation for cell Q3. Now it works in the way that when I type a new value in cell O3 it will pretend that I typed "Other" and generate a list from G40:G47.

    HOWEVER!! now when I type a known concept in (or select it from the dropdown) cell O2, I can't get a list to generate in cell Q3.

    Any suggestions anyone? I was thinking about changing the name of this thread to "Long and drawn out data validation issue" haha
    Attached Files Attached Files

+ 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. Issue with data validation
    By Ankor123 in forum Excel General
    Replies: 7
    Last Post: 01-14-2016, 06:39 PM
  2. Data Validation - Simple if you know how (I suspect)
    By seftonbarn in forum Excel General
    Replies: 2
    Last Post: 12-09-2014, 11:12 AM
  3. Issue with Data Validation
    By Nitefox in forum Excel General
    Replies: 5
    Last Post: 11-01-2014, 05:28 PM
  4. [SOLVED] Data Validation Issue
    By ZooTV92 in forum Excel General
    Replies: 6
    Last Post: 03-04-2013, 11:37 PM
  5. Excel 2007 : Data validation issue
    By fastbiker in forum Excel General
    Replies: 2
    Last Post: 02-01-2010, 07:10 PM
  6. Data Validation issue
    By cispus78 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2009, 06:14 PM
  7. Simple Data validation
    By piterfun in forum Excel General
    Replies: 2
    Last Post: 01-12-2009, 05:43 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