Formulas after filtering - Subtracting values when filtering lines

Here is another take on it. It includes a table where you can pick which maintenance item you would like to see. This controls the filter.

Note that the columns in my table are not all the same as yours. I used G for the mileage difference

Table 2::B1 is formatted as a popup. The easiest way to make it is to start with a longer table (more rows), enter all of your items in column B, sort the column, select them all, format as pop up, then delete all rows but the top one. You can use the same popup in column A of Table 1as the default, which will ensure no typos that would mess up the calculations. Set it to "start with blank" when used in Table 1.

Formula in Table 1::G2 =D2−XLOOKUP($A2,A$1:A1,D$1:D1,D2,0,−1)

Fill down to complete the column

I formatted the column with a custom format to not show zeros or negative numbers. Negatives will happen when you enter a maintenance item in A but have not yet entered an odo reading in D.

Formula in Table 1::H2 =IF(OR(Table 2::$B$1="All Items",Table 2::$B$1=$A2),TRUE,"")

Fill down to complete the column

Set up a filter for column H that shows rows where text is TRUE. In the filter shown below, "Show?" is column H. It got that from the header row.

Hide column H when it is all set up.

Use the "All Items" item in the popup to see all rows.

I'm assuming that the table has been filtered to show only the rows containing "Air Filter" in column A, and that your question could be rephrased as:

Show the difference in between the values in column D of this row and the next row containing the same value in column A as this row.

One way to do that is to add an index column to the table (column K) to contain an index value consisting of the text in that roe of column A and a serial number counting the number of times that text appears from the top of the table to 'this row'.

With this column hidden, and the table filtered to show only rows containing Air Filter in column A, the table looks like this (with a new Air Filter row added and odometer reading entered).

The error flag in row 14 is due to there being no value in column K to match the index value "Air Filter0".

In use, the column containing the index (K) would be hidden as above.

Here's a second screen shot, showing all columns including K (but still filtered to show only Air Filter rows.)

The formula shown below the table is entered in K2 and filled down to the last non-footer row. It creates the index value for each row using the text in 'this row' of column A and the count of appearances of this value from the top of column A to 'this row' of column A.

The formula is 'live' for all body rows of the table whether it is filtered of not. Data entry must be done with the full table showing, as the filter is based on the content of column A matching the filter value. Unless the last row (before the footer row) contains the filter value in column A, a row added below that row will not be the new 'bottom row', but will be inserted directly below the current (filtered) bottom row.

This might be handled by adding a second rule to show row(s) whose column A cell is blank, setting the filter to show rows matching 'any rule', then taking care to insert the new row by selecting a cell in the intentionally left blank row and pressing option-up arrow to insert the new row above the intentionally blank row.

(The 'intentionally blank row needs to be blank only in column A and in columns where it's content would affect the results in the Total row.)

A question: What is to be totaled in the Total row?

As set, changing the filter key to show a different piece of maintenance requires entering that name in the filter panel. It's possible, using another auxiliary column, to define a single filter rule using that column and a single cell on the main table or on a separate table in which one could choose ALL or one of the maintenance items from a pop-up menu to control the content of the auxiliary column and from that the filtering of the table.

Regards,

Barry