Automatically change validated entries when source of validation list changes

{ 90 Comments }

By Ashish Mathur · · · Leave a comment

After creating a validation list in a cell, one may enter one any value in that cell from the cell drop down.  Once a selection is made in the cell drop down, a change in existing source entries (say a spelling change) of the validation list will not cause the cell entry to be changed (although the cell drop down values change).

To change the cell value when the validation source entries change, refer to the this workbook.

Leave a Comment

Your email address will not be published.

CAPTCHA

*

      • he fist sheet is “List” where I would like to see values from drop down menu in columns U and columns AW. The source for the data validation list is on the sheet “List” as you can see.

        I set up the validation list procedure based on your article on contextures that uses counter, uselist, valdata and it works for me perfectly.

        The only problem that i have now is:

        If you look at column U, you should get the data validation drop down menu, so for example based on ID in column T, for the cell U6 you get the proper drop down menu based on source and ID, it works, I checked it, no problem.

        MY PROBLEM IS THAT FOR EXAMPLE IN CELL U11, you have also drop down menu but you dont see the first value from drop down menu until you click on this cell and select something. And this makes me crazy because you have to always go and click and select something and I would like to have some macro that automatically assign the first values from the list to drop down menus in columns U and AW, without clicking on it. You just click if you want to change but automatically you will have column U and AW full with the first item from drop down menu..

        Do you think it is possible or not? I did not manage anything.

        Many many thanks for your help!

        Reply

        • Hi,

          Sorry for the delay in replying. You may refer to my solution in this workbook. As you add entries in column D, the cells of column V and AW will show the first item available in the respective validation lists.

          Please note that I have also reworked your naming conventions.

          Please review and revert.

          Reply

          • Dear Ashis, it its awesome. This is exactly what i need, however one small adjustment would be great if it is possible – now you have to write something to column D and then you will get the drop down menu. But for example if I copy to column D data to 10 cells, the drop down menu appears only in the first one and for the other cells there is nothing. Do you think it would be possible to fix it? Thanks a lot, I really appreciate it

          • Dear Ashish

            Please how can I make changes Columns name or
            tab names I tried to assign the new values to the following(I have two Tabs OSA, and Data:

            For count_cells = 1 To Range(“E6”).CurrentRegion.Rows.Count – 1
            Set rng = Worksheets(“OSA”).Range(“C6:C16”)
            If Intersect(Target, Range(“E” & count_cells + 1)) Is Nothing Then

            Please help
            Regards

  • Thanks for this, it is very useful.
    I can get it to work just fine when the drop down list and the rest of my data are all on the one Excel sheet.

    However, how do I get it to work when the drop down list is on a separate sheet to the rest of my data.

    example:
    Sheet1 is called ‘Statistics’ and it has my drop down list to choose from.
    Sheet2 is called ‘My data’ and it has all the content I want to display including the information from my drop down list from ‘Statistics’.

    Scenario: I would like to change the spelling of cells in ‘Statistics’ and have it automatically update in the sheet called ‘My data’.

    Any help is most apprectiated, thanks in advance =)

    Reply

          • Hi, I have found an issue.

            Example:
            If my drop down list has:
            Obtain from
            Obtain from source
            Obtain from client

            And I change “Obtain from” to “Get from”, so my list becomes:
            Get from
            Obtain from source
            Obtain from client

            I find that everywhere I have used “Obtain from source” & “Obtain from client”, suddenly changes to “Get from source” & “Get from client”, even though my drop down list still has “Obtain from source” & “Obtain from client”.

            Is there a way to fix this?

          • Hi,

            In the VBA window [Alt+F11 > sheet3 (Validation)], change this line of code

            rng.Replace What:=old_value, Replacement:=new_value

            to

            rng.Replace What:=old_value, Replacement:=new_value, LookAt:=xlWhole

          • Thanks Ashish, that works perfectly, thank you.

            FYI: If you then want use Control-H to replace text, then it will no longer work since the VB code interferes with this routine. Excel gives the following error:

            “Microsoft excel cannot find any data to replace….”
            Even though the data/text is clearly there =)

      • Thank for the solution posted above. The code works great, however, I did come across a runtime error 1004. In the code I changed the range to E:BQ (Range Name: PLORange) and on the data entry page the info was filtered. I’m guessing that is probably why it caused this error. My question: What do I need to change in the code to handle data validation entry changes on filtered data?

        Reply

          • To clarify – When users go into the spreadsheet they must first filter by their program. When this is done, the learning outcome field will only show items based on the program chosen. Any changes to the learning outcome field must be done on the ListInfo page. I would like these changes done automatically to existing entries on the Data-Entry page. I would also like the ListInfo page to be dynamic and sorted automatically (maybe using the INDEX function?). Hope this helps.

          • It does work. THANK YOU!

            I also wanted the columns on the ListInfo page to be dynamic ranges (they currently are not) using INDEX instead of OFFSET but it is giving me problems. This is what I have come up with so far.

            =INDEX(Dropdown-ListInfo!$E$2,0,0,COUNTA(Dropdown-ListInfo!$E:$BQ),Dropdown-ListInfo!$1:$1))

            I also included the following code on the Worksheet_Change event because I want to auto sort each of the columns independently. Am I on the right track with this code?

            If Not Intersect(Target(1, 1), Range(“PLORange”)) Is Nothing Then
            Range(“PLORange”).Sort Key1:=Range(“PLORange”), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
            End If

            Please advise and thank you again for all your help. I appreciate it more than you know.

          • You are welcome. I guess what you want it to include new items (rows) that you add to each column from E:BQ of the Dropdownlist-info worksheet in the drop downs available in column G of the Data_Entry worksheet. My suggestion would be convert each individual range into a Table. So E1:E7 and press Ctrl+T. Then select F1:F14 and convert that also into a Table. Do this for all individual ranges. Once this is done, if you add any entry/entries, that/they will automatically appear in the validation drop down.

          • I initially made it a table and wanted to keep it that way but had to change it back because the workbook will be shared and Excel doesn’t allow tables in a shared workbook. Is there a work around for this or do I just have to keep it as a range?

          • You will need to keep it as a range. Now the only option is to write individual OFFSET formulas for all columns till BE which will determine the last filled up cell in each column. Or else one can also write a VBA code to auto create these OFFSET formulas.

          • I have read that OFFSET is volatile and therefore wanted to try the INDEX function instead for all the columns. Do I have the following set up correctly because I have tested it but still having problems. It is :
            =INDEX(Dropdown-ListInfo!$E$2,0,0,COUNTA(Dropdown-ListInfo!$E:$BQ),Dropdown-ListInfo!$1:$1))

      • Thanks for the solution. What you did is exactly what I’m trying to do as well…however I can’t figure out how to add this macro to my worksheet–can you help? Thanks!

        Reply

  • How can I make the source list be linear or horizontal oppose to being vertical within the VBA code? I want to be able to drag the row down if I need too. I am assuming that the VBA will not need to be updated with this workflow? Is this VBA per worksheet code or can it be for a whole workbook?

    thanks!

    Reply

      • Thank you for your revision! The Values for the List (Horizontal Data), I would like to use a drag down method. But It only updates the first row of the data. Can the macro be changed to update whats automatically no matter how many rows are dragged down?

        Reply

          • For any example. Lets say A1=Data List, C1=Data List, and E1=Data List (pull downs). AA1(to DD1)=Data Validation List Values. With my project that I am working on. I would like to be able to Drag Down Row A down to Row 24 (or so). I would like the data validation list values to be re-lative to the Row. So I can change the values of Row 1 and it only affects A1, C1, and E1. If I change the values for Row 20, then it only affects A20, C20, and E20.

            I hope that makes sense.

          • Everything seemed to work. But as soon as I dragged down a few rows, I received an error “new_value = Target.Value”.

          • Hi,

            What exactly are you trying to drag down. I selected Critical6 in cell B12. I then dragged this down till B21 to generate entries till Critical17. I then changed then entry in cell F18 to Critical123 and I could see the commensurate change in cell B18. No errors anywhere.

          • Hi,

            I downloaded your file and I get no errors at all. I tried the following:

            1. I selected B21 and dragged down without any problem
            2. I selected A21:D21 and dragged down without any problem.

            I am running Excel 2013 64 Bit with Windows 8.1 64 bit on my system. The version of MS Excel and/or Windows should not have any bearing on the working of the solution. Just ensure that the VBA window is closed when dragging down/making any changes.

  • Hi!
    This is exactly what I need, especially the one you submitted with the filename “automatic-validation-entry-changes v1.xlsm”. I have two sheets too, have one for the source. The thing is, I never used macros before. So I have your file, and don’t know how to transfer this macro to my document so I can use it too. I am in excel 2010, when I click View -> macros -> View Macros, it doesn’t show any macros. How can I make this work?

    Reply

    • Also I should add, that more columns use the same sources. Basically the source is a 5 item scale from very dissatisfied to very satisfied, and I use this for 4-5 columns.

      Reply

    • Hi,

      You can view the code by press Alt+F11. Double click on Sheet1 (Source) in the Validation window. Copy the code from there and paste it in the same location in your workbook. That’s it.

      Reply

  • I have one worksheet containing the source data and a bunch of different worksheets that utilize the source data (not just one). I’m struggling to figure out how to get the code to reference and then automatically update all of the worksheets at once.

    Any help would be very much appreciated!!

    Reply

  • Hi, the code works great to update the drop down box but I want to use a formula in my list to auto update the list values depending on certain cell contents rather than inputting something manually. However, with the VBA code it automatically deletes the formula in the cells for just text. Any way around this?

    Thanks,

    G

    Reply

      • Hi Ashish, thanks for the reply.

        So I have a normal drop down box linked to a list (the list is in a separate sheet to the drop down).

        In the list which links to the drop down I have a formula which automatically changes some of the values within it. If certain criteria is met some options available in the list then change. i.e. IF(A1=”YES”,”NO”) etc.

        What I would like to happen is that when a value in the list is automatically changed by the formula it is automatically changed in the drop down cell as well.

        The solution above works if the list is manually updated but not if the list contains a formula as it overwrites any formula in the cell with just text.

        Is there a way around it? I tried to explain it in the above spreadsheet adapted from your previous example.

        Many thanks for any help,

        G

        Reply

          • Thanks Ashish. Hmm I wonder if there is a copy and paste macro I could use to copy from the cell with my formula in when it changes to the one in the list linked to the drop down? I’ll have to do some more googling!

          • Automatically change validated entries when source of validation list changes
            Hi Ashish
            I am using office 2011 for Mac. I downloaded your workbook but it doesn’t work for me. Any changes that I make in F7 do not update in the B column.
            Any help with this would be greatly appreciated!

  • Hi Ashish,

    I’ve been playing around with the old problem I mentioned to you before – changing a list based on a formula which then automatically changes the corresponding dropdown. I’ve tried to do a copy and paste function to update the list but I can’t get both functions to work.

    Its a simple YES/ NO drop down I’m trying to auto update. Basically if Option 1 is YES I want the box to automatically limit Option 2’s choice to just NO. If Option 1 is NO then Option 2 can be YES or NO.

    Any ideas? Thanks.

    See below.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    Dim count_cells As Integer
    Dim new_value As String
    Dim old_value As String
    Dim rng As Range
    For count_cells = 1 To Range(“U13”).CurrentRegion.Rows.Count – 1
    Set rng = Worksheets(“Complex Timeline”).Range(“E155”)
    If Intersect(Target, Range(“U” & count_cells + 12)) Is Nothing Then
    Else
    Application.EnableEvents = False
    new_value = Target.Value
    Application.Undo
    old_value = Target.Value
    Target.Value = new_value
    rng.Replace What:=old_value, Replacement:=new_value
    Target.Select
    End If
    Next count_cells
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    End Sub

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address “$U$18” Then Exit Sub
    If Target = “YES” Then Range(“$U$14”) = “NO”
    If Target = “NO” Then Range(“$U$14”) = “YES”
    End Sub

    Reply

  • Hi Ashish,
    Got error (application.undo) when i try to paste cell with formula (concat)

    my formula =IF(BuildMaster!$A12″”,BuildMaster!$A12&” “&CHAR(10)&BuildMaster!$B12,””) –>BuildMaster is sheet name.

    after combine 2 cell into 1..then i write vb to copy and paste it to your severity options but the dropdown list not updated.

    Please help..

    Reply

  • Ashish,
    Here is the file.

    http://www.filedropper.com/automatic-validation-entry-changesv1

    First:
    go to buildmaster sheet, update host and IP.
    then click update.

    Second,
    go to source sheet, Cell B12 with formula =IF(BuildMaster!$A12″”,BuildMaster!$A12&” “&CHAR(10)&BuildMaster!$B12,””) successfully combine 2 cell into 1.

    third.
    after that my vb script should copy cell B12 and paste it to A12 but got error.

    Appreciate if you can help me to check is where is the mistake as the dropdown will not update.

    Reply

  • hello, thank you for your helpful article, but I still face a problem that my validation data is Horizontal and in a different sheet, so I need combination between 2 of your codes, beside that the code should be in a sheet called premiums, as I can’t copy the same code to more than 50 sheets
    so my validation data is in “premiums” from U2:AN2
    hope you can help me

    Reply

  • Hi,

    I love your solution and it works fine in the workbook that you provided.
    However my dropdown value lists are in a second sheet called “choices”, while the sheet where the dropdowns are located is called “Rem. Plan”.
    I’m not a programmer so I tried to modify your VBA code to fit it to my situation.
    No luck so far and any help would be greatly appreciated.
    Thank you in advance !

    Reply

  • This is great! But i am trying to refresh my combobox from a cell that has the “concatenated” formula from 2 other cells. When i enter the value the first time, it auto updates. but when i chance a value from cell any of the 2 cells, the concatenated cell changes, but the cell capturing the concatenated data does not autoupdate.
    e.g Source is cell A. Concatenated cell is B with values from D+E. D is x value, E is y value.

    Reply

  • Hi Ashish,
    Thank you for sharing your code. I wonder if this will work for different tabs?
    Can you suggest a VBA code for such a situation:
    Validated results are shown in Tab “US” in Column A2:A1000
    List option values are in Tab “SR” in Column A2:A30
    Thank you!

    Reply

  • When referring to the sample worksheet provided above, I had to add “Application.Calculate” to the VBA code. My sheet where the validation was would not reflect the new values until I added this code. The old validation values still showed until I would edit one cell on the sheet, forcing it to recalculate. With the added line of code it works immediately now. Great helper code.

    Reply