Secondary validation cell entry to update when primary validation cell changes


Having created primary and secondary validation drop downs, the issue is that when a primary validation cell changes, the value in the secondary validation cell does not become blank (or change automatically to first entry of the secondary validation list).  Instead the secondary validation cell remains the same as what was already selected earlier.

In the this workbook, i present the following three cases of dealing with the secondary validation cell value when the primary validation cell changes:

1. The moment the primary validation entry changes, blank out the secondary validation cell
2. The moment the primary validation entry changes, show the corresponding first entry of the secondary validation drop down
3. The moment the primary validation entry changes, perform the following:
a. If there is only one corresponding entry in the secondary validation drop down, then show that entry (no need to select); and
b. If there are multiple corresponding entries in the secondary validation drop down, then blank out the secondary validation cell

RSS 30 Comments…

 Share your views
  1. Here's the sample file where the update does not work in the second list when changingn the first.

    • Hi,

      You may refer to my solution at this link

      Hope this helps.

      • Hi,

        thanks. (The file was blocked by our filters.) I got the file now, and it works in your .xlsm! How did you do it? What did you change? I couldn't find any macros in the file.

        • Hi,

          There is a Worksheet Change event macro in the file. Right click on the tab name of the first sheet and select View code. In the left hand side panel of the VBA window, double click on the first worksheet and you will see the Worksheet change event code in the right hand side window.

          Hope this helps.

          • I see, great.

            What I sent you was a sampe workbook, but I copied the VBA code to my master XLSM and adapted it a little bit. In my case, C2 contains the first validation list and K2 the second, dependent list. Since these lists are repeated in each row (C2, C3, C4 versus K2, K3, K4 etc), I wanted this change event to always occur for these columns.

            I tried the following, but it gave me an error:
            Private Sub Worksheet_Change(ByVal Target As Range)
            If Not Intersect(Target, Worksheets("Overview").Range("D2", Range("D").SpecialCells(xlCellTypeLastCell))) Is Nothing Then
            Worksheets("Overview").Range("K2", Range("K").SpecialCells(xlCellTypeLastCell)) = ""
            End If
            End Sub

            Could you perhaps tell me where I went wrong? (FYI: I am not a programmer)

            Thanks for the invaluable help!

          • Please upload your live workbook to SkyDrive and share the link here.

          • Thanks, but I can't upload the live workbook for data security reasons. The validation lists are the same as in the sample I sent you, just that the selection lists B3 (Tools) and C3 (Persons) are repeated in every row. Thus: if B4 is changed, C4 should be reset, if B32 is changed, C32 should be reset and so on. Any suggestions about the code change?

          • Hi,

            You may refer to my solution in this workbook

            Hope this helps.

          • Hello Ashish,

            it worked! I adapted it to my live workbook and it worked like a charm. THANK YOU!!!

  2. Hi

    I am trying to create a report with the following critera

    8 columns

    20,000 rows

    The first column is destinations (there are around two dozen of these)

    The other columns have lists of customer names, supplier names, and numerous other stats

    I can list the destinations but would then like a second list dependent on the first ie only the customer names that have used the selected destination become available and then a third list dependent on both the first and second ie only suppliers that have used the selected destination and customer combined are available. I would then sumif the remaining stats to create charts and data sets

    Manually naming the lists is far to big a job using the 'name manager' function as this is something that will change daily and a report I need to be available with relatively limited timescales

    Hope someone can help as this has been a real pain trying to figure out



  3. Hi,

    Thanks for your reply from yesterday. I was able to get the code to partially work, but I need some more help to perfect it. If I used the code from your workbook (after altering it for my workbook) I can get it to default values for Round 2, but it does this for all values of Round 1. I only need it to do that for a Round 1 value of "Pass". When I replace the "Is Nothing" part of your code with "="Pass"", it fails. Here is the code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Worksheets("Overview").Range("F2:F55")) = "Pass" Then
    Worksheets("Overview").Range("H" & ActiveCell.Row) = "N/A"
    End If
    End Sub


    • Hi,

      You may refer to my solution in this workbook. Select from the validation drop downs available in range F2:F55 of the Overview worksheet and the dependent validations in range G2:G55 will change as per your rules.

      I have had to combine the code from my solution available at this link as well.

      Hope this helps.

  4. Sir, Why does the INDIRECT statement show an error when the macro code recorded by performing data validation for a drop down list dependent on another cells' value work properly during recording?

    • Hi,

      Please tell me what you want to accomplish. What is your dataset like and what do you want to do. If the macro recorder is not working, then we can change the VBA code to get what you want but for that you have to clearly define the problem first.

      • Sir It goes this way: Firstly i write down Manufacturer Name in a Cell (D1) and then I write down entries like Texas (D2) and LT (D3). I name Cell D2 and D3 collectively as Company.
        Then in another Cell (E1) I write Texas Instruments and Input Names of 3 ICs in columns E2, E3, E4. I then collectively Name the 3 entries as Texas.I follow the same procedure in cell F for Linear tech and name the entries as LT.

        Then I start recording my macro: Cell A1 is named Company Name and a Range (A2:A6) is selected and I perform data validation using =Company. This gives me a drop down list under Company Name (A1) to select any of the 2 options namely Texas and LT.

        Then Cell B1 is named IC name and a Range (B2:B6) is selected and I perform data validation using =INDIRECT(A2). This allows me to select my options under IC name depending on what i choose in the corresponding column cell of A.

        But the problem arises when i re-run the macro code generated citing error 1004 for using INDIRECT. Please Help

        • Hi,

          You will not be able to accomplish this with the Macro Recorder. The VBA code generated by the Recorded Macro will have to be tweaked to get your desired result. I have shared the VBA code for the same (3 cases) in the original post itself - you may download the workbook and try to plug in my VBA code on your data.

          If it does not work, you may share your file with me - upload your file to OneDrive and share the link of the uploaded file.

  5. Sir this is the Link from where i learnt data validation and you also did share the same link your Excel sheet.

    But the INDIRECT(A2) is the statement where the error actually pops up when the generated macro is re run

    • Hi,

      That is exactly what I said earlier - you will have to tweak the VBA code to get what you want. In the file which you can download from my original post, you may see my VBA code. If you are not clear of how to apply it, please share your file with me (upload the file to OneDrive and share the link of the uploaded file here)

  6. This looks like a really old post, so hopefully it's still being monitored, somehow.

    I am trying to conform this programming to my file, but can't seem to get it to work (I have very little VBA knowledge). For the combined validation (where 2nd box auto-changes if only one option, or clears if more than one to avoid mismatches), I have only one dropdown (not 3) and the secondary field is actually below the 1st (same column, different row) instead of to the right. I attempted to just change the range on the "If Not Intersect" line to the single cell (in my case "B4:B4") which seems to work (debug doesn't stop there). Here's my second line - the one debug doesn't seem to like:

    entries_in_range = Application.WorksheetFunction.CountA(Range(Range(ActiveCell.Column & "4")))

    Perhaps this is all a little much, and I can reduce this line, but I'm having little luck.
    So, cell B4 is my drop-down, and cell B5 is what I need to change or clear. Any help is appreciated.

  7. Hi!
    How does I protect my dynamic drop down list? In the first column I made a dropdown list with data validation. In the second column a second drop down list with =INDIRECT. My problem is that in the second list I can manually write inside the dropdown list and I dont want it.

  8. Hi Sir,
    I saw this post and found to be very useful. I implemented the macro code and the dependent drop downs. However, I have an issue.
    When I enter data in K3 cell cell using the drop down, then corresponding linked cell L3 will show the correct related entries in dropdown and it works correctly.

    However, after filling K3 and L3, when I enter data in cell K4, the L3 cell gets reset. I dont want this to happen. I want the data chosen in K3 and L3 to be there while I enter data in K4 and L4 and so on.. Can you please help me sir?

    Here is the macro code I used. I am not a programmer. so not able to figure out what to do. Request you to please help.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Worksheets("Data").Range("K3:K501")) Is Nothing Then
    Worksheets("Data").Range("L3:L501") = ""
    End If
    End Sub

Leave a Comment

Your email address will not be published.