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 Renate. I am an Excel user like you.

Someone may be able to help with a VBA solution to do this on your entry sheet, but here is a way you can accomplish what you want using a formula on a new sheet that will always be automatically sorted as you make updates on your entry sheet. I started with this mock data.

.

Open a new sheet and rename as desired. Copy your headers onto the new sheet. Then, in A2 (modify this if necessary for your layout) enter this formula.

=SORTBY(Sheet1!A2:N100,Sheet1!N2:N100,1)

Modify the ranges as appropriate. I included more rows than I had data to allow for additions to the table if that is something you need. If your data is a fixed range just use that range. The formula will automatically update any time a change is made on your source entry sheet.

.

The formula fills in 0's for all cells that do not have data. To remove them go to File>Options>Advanced>Display Options for This Worksheet and un-check Show a Zero in Cells That Have Zero Value.

Answer 2

Hi Rich M,

Thank you very much for your suggestion. I think however it would be too inconvenient having to create an additional sheet. I hope there are anyone out there with a VBA solution.

Kind regards,

Renate

Answer 3

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.

Renate,

"date is changed" means nothing. Does the cells in that column contain real dates (value in the cell) or are the dates calculated by a formula?

Andreas.

Answer 4

Sorry, I am no excel-expert so I do not know what details are critical and not here.

But to answer your question, the dates are manually changed, no formulas involved here.

Renate

Answer 5

1. Rightclick at the bottom left on the name of the sheet with your data and choose: 'Display - Programcode'. Now, you are in the vba-editor.
2. Copy the vba-code below and paste it on the right.
3. Close the vba-editor by clicking on the cross at the top right.
4. Save your file as *.xlsm-file (Excel-file with macro's).
5. Change a date in column N. Private Sub Worksheet_Change(ByVal Target As Range) Dim lr as integer lr = Range("a" & Rows.Count).End(xlUp).Row If Not Intersect(Range("n2:n" & lr), Target) Is Nothing Then Range("a1:n" & lr).Sort Key1:=Range("n1"), Order1:=xlAscending, Header:=xlYes End If End Sub

Edited at 10.06 h.