Data Validation List and sorting

    Hi all,


    I currently have a list of companies in the first row, and activities underneath for each company. This is then used as a data validation for cells. However, if I add a company in, then sort left to right, the existing data validation doesn't update with the new sorted address... Do I need to recode and update every drop down if I do a sort? Reason being my client likes to look at the company activities and wishes to view it (my code just added on end then sorted - but now causes this problem). I would say this is a bug that needs fixing because if you are sorting the "validation list", then the validations should update accordingly. Have I missed a trick here? I thought that by removing the $ the validation would work but it doesn't - though if I add or remove a column the validation works correctly (so why not sort MS)


    Any help or ideas?

    Quote from Kenneth Hobson

    Did you set a named range and used that?


    Apologies I had trouble logging in and the site is very slow.


    I recoded it to use named ranges - and found that it still does the same thing! It looks like if anything gets added to my set up table and then sorted alphabetically left to right, I will have to redo all the validations for all affected cells in the data sheet using those validation lists. Im also tempted t take out the named ranges again as I prefer to code in the cell refs. This really needs fixing by MS.


    I guess the best way is to flag if something is going to be added (my code checks it is on the setup company or activity table), then if so not to do the validations as its importing in, but to wait till the end then rerun for all.

    IF I add an ending column, and name the range to the left of it, it works fine for me. Any insertion in the named range and sorted is shown in the data validation's list.


    I guess one could do it all based a sheet's Change event. The macro could sort, and remove duplicates, and remove blanks, etc. Or, if you want to sort manually, simply delete the named range and re-add it.


    You might want to attach a short example file if you want to pursue a solution similar to that above.

    Hi Kenneth


    so the process works as follows (it is for a builder who has multiple sites and plots).


    The accountant spreadsheet gets dumped onto an input sheet
    User presses a button to clean and then add into a data sheet automatically
    if there is a new supplier, it adds this to a company sheet, and colours in the data sheet so the user will know it needs to fill in
    when returning to the data sheet, there should be a drop down list only showing what is in the corresponding drop down


    it all works well enough. However the user might want to change the set up, and hence why it needs to be sorted. My solution for this was to flag if the company or activity sheet is sorted, then to add back validations for the entire datasheet. It seemed overkill, but we are only talking a few thousand rows so better to be safe.


    interesting that you said sorting the columns left to right works for you, it doesnt for me. The project endedup a few thousand lines of code so another dozen wasnt too arduous!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login