Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Data Validation - List change

    Hi All,

    I have a list of names in a list that is applied to data validation from a dynamic range "=OFFSET(Lookups!$C$2,0,0,COUNTA(Lookups!$C$2:$C$5 0),1)"

    I apply this list in numerous places in a number of worksheets via data validation List.

    The question is if I change a name in a dynamic range list (say from the word "Black" to "White") is there any way that this change can be made to make global changes/updates across all worksheets?

    I hope that makes sense.

    Reagrds

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It could be done with code. You'd have to loop through all validated cells, check if the cell value is the old value, and the validation formula is the one you're looking for, and then update accordingly.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Rory

    Sounds like a pretty complex task - may need to be the old manual way (?)

    Regards

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    It shouldn't be that hard to write. Is that formula entered literally into the DV source, or do you use a named range?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts

    Updating validated cells

    Hi folks
    I started playing as a learning exercise.
    Is there any merit in the following approach?
    This is not offered as a solution but more for comment as part of my learning. There will be bugs.

    The intent is to loop through cells in the usedRange. (problem #1 I guess)
    On each pass check for validation and if conditions are met substitute old value for new value.

    As it stands the new value could fail the validation.

    validations.png

    Code:
    Sub Test()
    CheckValidation "red", "green"
    End Sub
    
    Sub CheckValidation(sOldValue As String, sNewValue As String)
    Dim oCell
    For Each oCell In ActiveSheet.UsedRange
        oCell.Select
        With ActiveCell
            If isValidated And .Value = sOldValue Then .Value = sNewValue
        End With
    Next
    End Sub
    
    Function isValidated()
    Dim X As Variant
    On Error Resume Next
    isValidated = True
    X = ActiveCell.Validation.Type ' eight types values 0 to 7
    On Error GoTo 0
    If IsEmpty(X) Then
        isValidated = False
    End If
    End Function
    Comments are most welcome.
    Regards
    Geof

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    it would be MUCH easier to point the validation list at a named range then use VBA to define / redefine that named range.

    Doing so once will then be picked up in all the validations that use it.

  7. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Verada,

    I agree with Martin. Here is how I would set it up:

    Create a named range:
    Datavalidation1.png

    Select the cells you want then create the data validation:
    Datavalidation2.png

    Any changes made to the validation list (add/delete/change) would reflect in all the applied cells

    HTH,
    Maud

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Maud

    ..perhaps you and Martin are missing the point that Verada raises?
    It's not about the best way to define a data validation list.
    The question posed was about what happens to existing cells which have a currently valid selection from the data validation list (e.g. "Black"), when you subsequently make a change to that specific entry in the source list (e.g. changing the entry in the list from "Black" to "White").

    I find the best way is detect this is to use the 'circle-invalid' data option.
    z2.png

    Geoff's post#5 had the right approach, but he is probably unaware that in VBA, you can just use..
    ActiveSheet.CircleInvalid

    So, for cells that have an entry of 'Black' (as chosen from the 'old' data validation list), these will now be circled as 'invalid' i.e. no longer members of the current validation list (where 'Black' has been replaced by 'White')
    z1.GIF

    I suspect that Verada wants all those 'data-validation-Black' entries to be automatically changed to 'data-entry-White' values, as per her example.
    This could be done via vba as Rory suggests in post#2

    zeddy
    Last edited by zeddy; 2015-09-27 at 04:54.

  9. The Following 2 Users Say Thank You to zeddy For This Useful Post:

    geofrichardson (2015-09-27),verada (2015-09-30)

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Verada

    ..this example code will start you off:
    Place your cellpointer on any cell that has that data validation list assigned, then run this code:
    Code:
    Sub updateValidationCells()
    
    Selection.SpecialCells(xlCellTypeSameValidation).Replace _
        What:="black", Replacement:="white"
    ActiveSheet.CircleInvalid
    
    End Sub
    You would need to repeat this for each applicable worksheet.

    An example demo file is attached.
    Don't forget to place the cellpointer on a cell which has the data validation, before running the update vba code.

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-09-27 at 05:28.

  11. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2015-09-30)

  12. #10
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi folks
    Thank you .. every day is a school day.

    activesheet.circleInvalid is indeed new to me.

    Geof

  13. The Following 2 Users Say Thank You to geofrichardson For This Useful Post:

    verada (2015-09-30),zeddy (2015-09-27)

  14. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Geof

    ..a school day for me too! Manawatu-Wanganui is my new favourite placename.

    zeddy

  15. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2015-09-30)

  16. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Zeddy,

    I'm not sure what you meant by placing the cellpointer on a cell...

    I put my cellpointer on a blank cell of Sheet2 and clicked your "Circle Invalid" button (after changing one of the colors in your Lookups sheet). I got the same circles I got as if I had followed your instructions.

    3 other questions:
    - what purpose do the named ranges "tempx" serve?
    - on the Lookup sheet, what purpose does the formula in J3 serve?
    - what does Sheet3 do, if anything?

    Thks.

    Fred

  17. The Following 2 Users Say Thank You to fburg For This Useful Post:

    verada (2015-09-30),zeddy (2015-09-27)

  18. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi Fred

    You have forensic skills indeed.

    For the [circle invalid] and [clear circles] buttons, it doesn't matter where the cellpointer is.
    But the routine that updates the validation cells requires that you start it with the cellpointer on a cell that uses the data validation that is being updated.
    The routine Sub updateValidationCells() begins..
    Selection.SpecialCells(xlCellTypeSameValidation)
    ..so the Selection part refers to the current cellpointer cell location. If it isn't on a cell that uses the data validation, it won't find any cells (i.e. that's the purpose of the xlCellTypeSameValidation bit)
    The named ranges serve no purpose - I was experimenting with the best method of selecting all the data validation cells - I just forgot to clear then out before posting the demo file.

    Sheet3 was just another sheet with some sample data validation cells in slightly different locations. (Verada mentioned that multiple sheets were being used.) The idea was to show that you needed to run my update routine on each required sheet. A more general purpose routine could be used to do the replacement for all sheets in a workbook, but I left that for others to contribute.

    - on the Lookup sheet, what purpose does the formula in J3 serve?
    ..in Verada's post#1, there is a space between "5" and "0" in the formula:
    "=OFFSET(Lookups!$C$2,0,0,COUNTA(Lookups!$C$2: $C$5 0),1)"
    ..I just copied it to any ol' cell to fix the formula.

    Good spot.

    zeddy
    Last edited by zeddy; 2015-09-27 at 12:22.

  19. The Following User Says Thank You to zeddy For This Useful Post:

    verada (2015-09-30)

  20. #14
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Levin, Manawatu-Wanganui, New Zealand
    Posts
    324
    Thanks
    9
    Thanked 28 Times in 26 Posts
    Hi again

    Dynamic Range Names & use of Offset()

    Are there advantages to this function over the use of a range converted to a table?

    A Data Validation rule could reference a table name instead

    Something like this :- =INDIRECT("Table2[Town]")

    To my aging eyes this is a lot easier to read.

    Just wondering. Legacy issues?
    Geof

  21. The Following User Says Thank You to geofrichardson For This Useful Post:

    verada (2015-09-30)

  22. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    zeddy,

    Yes, I totally missed the point of what verada was asking. The question is not how changes affect the drop downs on choices to be made but rather on the now invalid choices that have already been made. Thanks for clarifying

  23. The Following User Says Thank You to Maudibe For This Useful Post:

    verada (2015-09-30)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •