Develop a ranged function that stops at the row above

Mike Broglio

I am trying to develop a function that will collect the maximum value in the same column for all rows above the current row. My initial formula was

=MAX(COLLECT([Sim Exp Yr]$1:[Sim Exp Yr]1, $[Email Address]$1:$[Email Address]1, $[Email Address]@row, $Certification$1:$Certification1, CONTAINS("SimLab", @cell)))

Which runs from the $1 row to the row above the formula row. So in this case, row 2, the formula runs from 1:1. When I copy to row 3 the formula runs from 1:2, etc.

The problem is that when the sheet gets sorted the 1:2 formula can end up on row 100, so it is only looking at 1:2 instead of 1:99. Taking off the specified row numbers includes the formula row in the range and results in a circular reference.

I saw answers to looking up specific values in the row above, but nothing about incorporating the 2 helper columns (autonumber and match) into a range, i.e. going from $[Email Address]$1:$[Email Address]1 to $[Email Address]$1:$[Email Address]RowHelper@row does not work.

Any advice on this?

Paul Newcome

You would use the auto-number helper column and the "Row" helper column that uses the MATCH function to output the row number. Then to incorporate this into your other formula, you would include a range/criteria set looking at the [Row Number] helper column and the criteria being less than [Row Number]@row.

=MAX(COLLECT(………………………………, [Row Number]:[Row Number], @cell < [Row Number]@row))

Mike Broglio

Still getting a circular reference error. Putting the formula into row 3, with RowHelper set to 2 or 3.

=MAX(COLLECT([Sim Exp Yr]:[Sim Exp Yr], $[Email Address]:$[Email Address], $[Email Address]@row, $Certification:$Certification, CONTAINS("SimLab", @cell), RowHelper:RowHelper, @cell < RowHelper@row))

Paul Newcome

You have to put the formula into a column that is not being referenced by the formula.

Mike Broglio

This turned out to not work well. Since data is in column 1, for example, you are saying the result/formula needs to be in column 2. However, subsequent rows need to look in column 1, and now column 2, to find the max, and put the result in column 3. As you can see this creates a very large and widely distributed set of result columns, one for each row of data, and is not workable.

The solution I came up with is to create a mirror sheet that gets a copy of the rows on the main sheet once certain conditions are met. The main sheet formula, as described at the start, then performs the maximum lookup, all one column, on the mirror sheet, and returns the result on the main sheet.

If you have ideas on minimizing the number of sheets needed for this that would be helpful, but this is the best option I could come up with.

Paul Newcome

No. I think you may misunderstand. You do not need to keep increasing columns as rows are increased. This also holds up to having the sheet sorted.

=MAX(COLLECT([Sim Exp Yr]:[Sim Exp Yr], [Email Address]:[Email Address], @cell = [Email Address]@row, Certification:Certification, CONTAINS("SimLab", @cell), [Row Number]:[Row Number], @cell < [Row Number]@row))