+ Reply to Thread
Results 1 to 9 of 9

Using Dependant Validation. Show error or blank if Inital Validation Changes

Hybrid View

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    44

    Using Dependant Validation. Show error or blank if Inital Validation Changes

    Example Attached:

    I have set up a data vlidation & then a dependant validation.
    What I now want to do is:
    When I select a different initial Data Vaidation value
    that the cell which picks up the Dependant Validation value - goes blank or throws an error or something
    until I select the appropriate dropdown.

    This is to ensure that the appropriate Head & Sub are always used.

    The solutions I have found online are all based on VBA - which I know nothing about.

    Thank in Advance

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Dependant Validation. Show error or blank if Inital Validation Changes

    THe structure and layout of a sheet like this has a great impact on it.

    1) I've moved your lists into a Lists sheet
    2) I've given row1 a dynamic named range call Days
    3) This sheet is dynamic, you can add as many additional columns as you wish and add as many items IN each column as you want.
    4) On the Form sheet, I've used Days as the Data Validation source for column A
    5) In column B instead of INDIRECT() we are using an OFFSET() formula in the DV settings to do all the work. This formula is spotting the day selected in column A on the LISTS sheet to find which column it is in, then offsetting down to row2 of that column and then expanding downward to encompass all the entries. So it's dynamic, the second list creates itself.

    This solves the dependency issue, this will run itself consistently no matter how much you expand the columns and rows on the LISTS sheet.

    ================
    Then I applied conditional formatting to column B to turn the font in that cell white if you change a column A value and the B value no longer matches that lists.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-15-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Using Dependant Validation. Show error or blank if Inital Validation Changes

    JBeaucaire
    Thank you your instructions & examples were excellent.
    I did have to modify a little as I set the example up differently to my actual file.

    All went well with the exception of the Conditional Formatting &
    I wonder if you could again assist.

    It worked on some but not all and I think the issue is coming from the fact that I have a different number of fields than I had shown in my example.

    Where in my example I had
    7 Days - my doc has 32 options
    Max 3 tasks (under my days) - my doc has up to 64 options

    I tried to adjust the Offset formula but having never used it the Rows, Columns, Height & Width thing confused me & the variations I put in didn't work.

    Could you please give me a step for a hint?

    Thanks Again

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Dependant Validation. Show error or blank if Inital Validation Changes

    Just setup your lists exactly as I have shown on a LISTS sheet, then tweaking the formula takes little adjustment. I didn't see a workbook if you needed me to look at something. If you do post it, I'm going format the lists as I've shown, so you might as well do it. Exactly as I laid it out starting in A1 on a LISTS sheet.

  5. #5
    Registered User
    Join Date
    01-15-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Using Dependant Validation. Show error or blank if Inital Validation Changes

    JBeaucaire
    I'm sorry if perhaps I am doing something wrong here on the conditional formatting. I think it might be on the Offset Formula.

    I have attached another file which is set up exactly as my Actual documnet (except of course for the names etc - you might guess these are dummies to suit) but it's the exact number of fields in my document.

    I have also attached examples of the mistakes to try & assist.

    I grealty appreciate your help.
    Attached Files Attached Files

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Dependant Validation. Show error or blank if Inital Validation Changes

    When I opened your sheet and changed the country in A2, the city A3 disappeared as the CF painted it white. It's working great.

    http://screencast.com/t/H2kfnOkhw
    Last edited by JBeaucaire; 02-20-2013 at 03:19 AM.

  7. #7
    Registered User
    Join Date
    01-15-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Using Dependant Validation. Show error or blank if Inital Validation Changes

    Thanks for your video.
    I have used your CF formula showing True/False and Yes many of the combinations work however I am still having issues.
    I checked my lists just in case and Boomerang Rd def only appears in Australia.

    If when the country is Canada - I pop onto A3 (where it says Boomerang Rd) the drop down only shows those laces associated with Canada,
    it just doesn't turn Boomernag text white when these particular countries are selected.

    Could you perhaps try these specific examples just incase I am doing some thing wrong?
    I really do appreciate your help.

    How they are set on my actual doc *Changed range name to Country*
    Canada Day(Country)
    Boomerang Rd FALSE Task

    Example of Error:
    Correct Australia
    Correct Boomerang Rd

    Correct Change country to Barbados - Boomerang Rd vanishes (text goes white)
    Error Change country to either Canada, Dominica, Finland, Germany, Sweden, Turkey, Angola, - Boomerang Rd pops up again (text goes black)
    Attached Files Attached Files

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Using Dependant Validation. Show error or blank if Inital Validation Changes

    Bah! I left the MATCH's last parameter off, which makes it default to "fuzzy" matches. My bad.

    =NOT(ISNUMBER(MATCH($A3,OFFSET(Country,1,MATCH($A2,Country,0)-1,100,1), 0)))

    NOTE: Some of your lists are pretty long, you might want to increase the 100 to 1000.
    Last edited by JBeaucaire; 02-20-2013 at 10:46 AM.

  9. #9
    Registered User
    Join Date
    01-15-2013
    Location
    Auckland, New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Using Dependant Validation. Show error or blank if Inital Validation Changes

    Works perfectly.
    Thank you so much.
    Your initial response was a solution.
    My specific needs for this task were a little different - your patience in looking at my particular issue again to get it 100% was greatly appreciated.

+ 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