I have a data set that has the Subtotals applied to the dollar column for every change in X. I have a date column as well. Imagine starting with the subtotals collapsed to level 2 so that the subtotals are viewed as a summary showing only lines with "X Total" with the underlying detail hidden.
So starting from here, if you expand the + signs to see the detail that makes up each subtotal, some subtotals are made up of only one row, others are made up 2 or 3 rows.
Now collapse the outline back to level 2 (summary view). On each subtotal row (in the date column) I want to enter the MIN function to return the earliest date. For any given subtotal row, the function range would be the rows above, but also below the previous Subtotal. Because of this, sometimes the range will be across only one row. At other times, the range may span two or three rows.
In order to specify the range in my formula, I need to select the values above it with something like Cntl + Shift + Up Arrow to specify the range so that the last value in the range is the value right below the previous subtotal (like starting at the first cell of a range, then selecting all the way down to the last cell, or up to the last cell in this case). If I do this on a subtotal line that has 3 rows above it, when I copy and paste the formula down, that formula will not work for cases where I need to only specify one row above my formula to define the range for the MIN function.
Is there a way to grab the last value in a range regardless of whether or not the range spans 1, 2 or 3 rows?
So starting from here, if you expand the + signs to see the detail that makes up each subtotal, some subtotals are made up of only one row, others are made up 2 or 3 rows.
Now collapse the outline back to level 2 (summary view). On each subtotal row (in the date column) I want to enter the MIN function to return the earliest date. For any given subtotal row, the function range would be the rows above, but also below the previous Subtotal. Because of this, sometimes the range will be across only one row. At other times, the range may span two or three rows.
In order to specify the range in my formula, I need to select the values above it with something like Cntl + Shift + Up Arrow to specify the range so that the last value in the range is the value right below the previous subtotal (like starting at the first cell of a range, then selecting all the way down to the last cell, or up to the last cell in this case). If I do this on a subtotal line that has 3 rows above it, when I copy and paste the formula down, that formula will not work for cases where I need to only specify one row above my formula to define the range for the MIN function.
Is there a way to grab the last value in a range regardless of whether or not the range spans 1, 2 or 3 rows?
