Can I have a column of cells always automatically Sort by Date - Oldest to Newest?

[PitBoo]

I have one column on a spreadsheet that I would like to always have sorted by date newest to oldest (Q8:Q15000) whenever a filter is applied in any of the columns.


((only read this section if you want to know the root of the problem)))
The problem that I have and I don't think there is another answer for is that the sheet has certain columns that contain "link" formulas (example: =IF(I9>0,HYPERLINK(I9, "Link"),"") and it has to be sorted AND protected from people clicking in the cell. Problem is that if I use the uncheck "select locked calls" option when locking the sheet and use allow users to edit range, then no one can click in the cell when it turns into a hyperlink. So there is no way to lock this cell down and still be able to sort AND click in the cell because Excel has a known issue with this. And I had to use that link formula in that cell becuase if you don't do it that way then you SORT, then SORT another column by something all of your links get screwed up. This is a problem that is known and listed on Microsoft web site. That's the background.




Also the entire sheet is password protected. So whatever VBA is used it would have to be able to force the sheet to always sort a locked sheet (password is 78951). Again the trigger to sort like this would be whenever anyone applies a filter on any of the other columns.
Column headers are in row 7.
The sheet is A-X in number of columns, Q is the one that has the date. The sheet is 15,000 rows.

Thanks for any help.

[alansidman]

What about loading the spreadsheet to Power Query. Sorting in Power Query and then Close and Load back to your workbook. Any changes made to the original data will be updated correctly in the data output sheet from PQ whenever the workbook is opened if you set the connection to refresh on open.

[PitBoo]

Thanks for looking. I think that would be too complicated for the shop floor employees to use.
However if you wouldn't mind looking I created a macro with a button that just unlocks, sorts by that column then re-locks the sheet.

Problem is that it doesn't assign another lock password or ask for the original except the first time only, (due to not locking it with a password).


Not sure how to go about it, here is the code.

Please Login or Register  to view this content.

thanks again

[AliGW]

Thanks for looking. I think that would be too complicated for the shop floor employees to use.

I think your shop floor employees would be capable of using it. Alan said this:

Any changes made to the original data will be updated correctly in the data output sheet from PQ whenever the workbook is opened if you set the connection to refresh on open.

They wouldn't have to even know about the query working in tha background.