# Selecting Cells After Highlighted cells

Conditional Formatting & More

### #1 JPK1066

JPK1066

• Members
• 101 posts

Posted 22 June 2017 - 02:01 PM

I will attach a WB with a grid of numbers where I used Conditional Formatting to highlight all cells that are >= 25. What I would like to do is find all the values in cells after the highlighted cells for each team and see what the averages for those cells are for each team.

As an example I will do ARI.

This team had four such dates and I will display what those counts were and the counts for the ensuing day:

April 25th    29      24

April 27th    30      38

April 28th    38      33

April 29th    33      25

120 / 4   ='s 30

How can I filter those cells out?

Respectfully...........JPK 1066

### #2 Jonathan

Jonathan

• 887 posts

Posted 23 June 2017 - 11:57 AM

Hi JPK,

Taking a look at your workbook, it already appears to be in a 'pivot table' format with dates along the top and teams along the left column. While this is useful for reading purposes, it doesn't lend itself to filtering.

The first thing to do is to create a 'Date' column and rearrange the data into a more typical table format:

I did this quickly by using Get & Transform's 'unpivot' feature, covered in our Excel 2016 Expert Skills book (available from our books page), but it's possible that the source data is already 'unpivoted', or you could do this manually.

With the data 'unpivoted', the problem is almost exactly the same as one you've faced previously, in this thread.

You will need to add a column with an IF function that checks whether the value is greater than or equal to 25:

=IF([@Value]>=25,TRUE,FALSE)

The IF function is explained in depth in the video lesson: Expert Skills Lesson 3-5 Use the IF logic function.

Next you will need a more complex formula that checks whether the current row is greater than 25 or is immediately after a row that was greater than 25:

=IFERROR(IF([@[>=25]],1,IF(D1,2,0)),0)

I have used the values 0, 1 and 2 to signify:

0: Not greater than 25, not after a record that was greater than 25.

1: Greater than 25.

2: After a record that was greater than 25.

You could replace these with TRUE and FALSE if you don't need to filter them separately.

You'll notice that I also used an IFERROR function to suppress error messages on the first row of data. You can see more about IFERROR in the video tutorial: Expert Skills Lesson 3-23 Use an IFERROR function to suppress error messages.

Now that you have the data you need you can easily filter it using a simple filter, as shown in: Expert Skills Lesson 1-3 Apply a simple filter to a range.

I have attached my example copy with the filters applied:

I hope this is the solution you were looking for, but please feel free to reply if you need any more help with this.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

#### 0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users