For an applicable worksheet, EditDateColumn identifies the column for storing the date/time of each row's edit pick a column (A=1, B=2, etc.) that does not have any other data.ġ. This allows any cell edit(s) to be Undone prior to saving the workbook. Here is a solution that postpones storing the date/time of each row's edit until the workbook is saved. The previous solutions immediately store the date/time of each row's edit in a certain column of that row.
All have a problem: No cell edit(s) can be Undone (Ctrl+Z) because Excel clears the Undo stack when a Sub procedure changes a cell's value. (A row is edited when any cell in that row is edited.) Several solutions have been provided in the comments below. A dynamic list of the latest inspection results.Īs you add more data to your inspection list, your "result table" is updated to always show the latest inspection (see ) wanted to keep track of when a row was last edited to enable identification of rows with the oldest changes. What you end up with is a dynamic list of the most recent inspection results for each facility. Now copy cells F2:G2 down as many rows as appropriate for your facilities. This is not an array formula, so you can simply press Enter to put it in cell G2. =SUMIFS($C$2:$C$123,$A$2:$A$123,E2,$B$2:$B$123,F2)Īgain, the lower range references can be replaced with whatever reference is appropriate for your data. (Excel maintains dates internally as numbers.) To get F2 to look like a date, simply apply a date format to the cell. The result in cell F2 will be a number, which is actually a date. Also, you need to enter this as an array formula, meaning you press Ctrl+Shift+Enter to add it to cell F2. You can replace the two lower range references ($A$123 and $B$123) with whatever lower range is appropriate for your data. In the first column place a unique list of your facilities. (For this example I'll assume that these appear columns E:G.) Somewhere to the right of your data-separated by at least one empty column-place another set of identical headings. Further, the first row of your data contains headings (Facility, Inspected, and Rating). Assume, for the purposes of this example, that your data is in columns A:C, with A containing the facility, B containing the inspection date, and C containing the rating achieved on that date. Of course, you could also use a different approach that maintains the original data and simply extracts the information that represents the latest inspection dates. Thus, if you want to maintain the older information for historical purposes, you may want to perform the steps on a duplicate of your data. Understand that if you follow these steps it is destructive to your data-when completed, the older data is completely removed from your worksheet. Excel removes the duplicates and leaves only those records that contain the latest (most recent) inspection date. Make sure that the only field selected in the dialog box is the one that contains the facility.Excel displays the Remove Duplicates dialog box. With the Data tab of the ribbon still visible, click the Remove Duplicates tool in the Data Tools group.Using the controls in the dialog box, indicate that you want to sort first by facility (A to Z or Smallest to Largest, whichever is appropriate) and then by inspection date (Newest to Oldest).To use the tool for this particular purpose, follow these steps: Perhaps the easiest way to do this is to use Excel's remove duplicate tool. The result would be one row per facility, showing the latest inspection date. Gary needs to delete all the rows for each facility with the exception of the latest inspection date. This results in multiple rows for each facility, one row per inspection. Gary is using an Excel worksheet to maintain of list of facilities that his company inspects, along with the dates of all the prior inspections of those facilities.
0 kommentar(er)
