This formula can be used with a filter to show all rows that return TRUE plus an extra row at the top and bottom.

Let's say that you have an entry in column A:A and formulas in column B:B to return TRUE if certain criteria are matched.

Enter one of the below formulas in a cell like C4 and drag down. (Don't use cells too high or you will get #REF! errors for blank rows, you can adjust the below formulas if this is the case) Then use use the filter to select "1". If you don't write like "1" it's not too hard to rewrite the formulas to return something else instead.

= --OR(B4=TRUE,OFFSET(B4,1,0)=TRUE,OFFSET(B4,-1,0)=TRUE)

You can add or remove OFFSET functions to suit.

or make your formulas in column B:B return 1 instead of TRUE (add brackets and double minus marks as in = -- (TRUE), and use the below formula in column C.

=SUM(OFFSET(B4,-1,0,3,1))

Increase the -1 by multiples of 1 and 3 by multiples of 2 to add more rows.

The 'logic' behind all of the above is to veer away from fixed or specialized formulas and use more general formulas that are either enough to work with any criteria or need minimum maintenance to adapt to new criteria.

© Copyright
andrewsexceltips.net All Rights Reserved.