How to automatically sort data collections based on date

Question

Hello,

I have an excel file with information regarding equipment, having one equipment per row.

Information about the equipment expands from column A to N, where N is a variable date (date for next maintenance service of equipment, once maintenance occur this date is updated with the date for the next maintenance to occur).

I want the data collection to be sorted by "next maintenance date", but I am not able to get this to happen automatically when the date is changed. When I update the next maintenance date for an equipment (in the sorted list), the equipment row will not locate into its new correct position (based on date).

Is there a solution to this problem?

Thanks in advance.

Kind regards,

Renate

Answer 1

Hi,

Thank you very much for your answer. It works perfectly, except I am not able to get the orange colour to work. No matter what colour code I put in here for the code representing "one month in advance", it just turns out light green. I can however live with that, unless you see an easy explanation for it. When I try to change colours for the other dates, it works perfectly, so the only problem is this field.

Kind regards,

Renate

Answer 2

It works perfectly, except I am not able to get the orange colour to work. No matter what colour code I put in here for the code representing "one month in advance", it just turns out light green.

Then, you have changed the code, I think.

Note: The macro only checks whether a date belongs to the current month, to one of the following months, or to one of the previous months (as you asked). The day is not taken into account!

Answer 3

Hi again,

I have transferred the excel file over to SharePoints, however, the codes did not get included when I uploaded the file to sharepoint. It seems like the codes are not sheet-specific anymore, as the code field does not change when I switch between sheets. I tried to copy to code for sheet 1 into the new code field in sharepoint, this did not work at all, see pictures below of the code and failure messages.

I would really appreciate it if you have any suggestions here.

Kind regards,

Renate

Answer 4

I cannot help you with Sharepoints. But as far as your Excel file is concerned, there must be something wrong with your file. Have you checked that no conditional formatting is set in columns L and/or M ? You cannot use the macro and conditional formatting together !