Autosort

[giacc]

I am having problems with an 'autosort'. When the values in h4 change, h4 sorts appropriately. The text seems to sort with it..however I have 3 dropdown boxes in D, F, M that are not sorting with the rest of the row. Can you help?

Please Login or Register  to view this content.

Attached is a sample showing the before and after of the problem and the code.

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[giacc]

Ignore, trying to delete this post

[giacc]

A clarification, the cells that do not sort with the rows, that I referred to as dropdown boxes, are just cells with data validation where the user is presented with a list.
I am using a VB script to do the autosort.

The which are not sorting with the row have data validation and are also named cells.. Attached is a sample of the problem. I don't understand why they are not sorting properly. I have to roll out the full workbook tomorrow, this is the only thing left that doesn't work!

Hopefully it will make sense to someone on the forum! Thanks for any help you can provide.

[watersev]

hi, giacc, how the code can know of other dropdowns to account for their changes if under your current code the sorting is always done by H4 key?

[watersev]

there are errors in H column instead formulas

[giacc]

Thank you so much for responding. So my VB script is the problem? I am not a VB programmer, I found this code in a different post, someone trying to do an auto sort.

Please Login or Register  to view this content.

What is the proper way to modify the code to sort all of the fields? Do I enter a new linke for sortkey 2, sortkey3 or can I list the sort string on the one line?

[watersev]

ok, it seems you want to sort always on H4 key whatever change happens in "b4:m25", can you provide sample with formulas for test? Could you explain what should happen on the change in each column of the range "b4:m25"?

[giacc]

The code is in the sample workbook I attached.

[watersev]

sorting formula-calculated cells is a tricky thing, I suppose that is the root of a problem. What is the formula used in H column?

[giacc]

I am sure you are right. That is why I reached out for help, too tricky for me.

[watersev]

can you provide sample worbook with formulas in H column?

[giacc]

The forumla is (Or should have been) in the sample I attached. I am going to reattach.

The forumla in the first line should read:
=IF(AND(close1="Closed"),0,IF(AND(probability1="L",impactt1="L"),1,IF(AND(probability1="L",impactt1="M"),2,IF(AND(probability1="L",impactt1="H"),3,IF(AND(probability1="M",impactt1="L"),2,IF(AND(probability1="M",impactt1="M"),4,IF(AND(probability1="M",impactt1="H"),5,IF(AND(probability1="H",impactt1="L"),3,IF(AND(probability1="H",impactt1="H"),9,IF(AND(probability1="H",impactt1="M"),6,IF(AND(probability1="",impactt1=""),,)))))))))))

Let me know if the sample does not have the formula and the code in it.

[giacc]

What do you think, WATERSEV? Is it hopeless?

[watersev]

please check attachment. As I'm using Excel 2003 the dropdowns from Excel 2007 are not working and I have created new validation lists. Try to change Probability, Impact or Date closed.

Code logic: if value is changed in any of the following ranges: D4:D11 or F4:F11 or M4:M11 the code calculates value (as per your formula posted above but not on the sheet) and insertsresult in the same row column H and makes sorting on H column. The code is in sheet module. Hope this helps.

[giacc]

Thanks so much for looking at it, I will review it tonight.