Hi

I have over 18,000 lines of data which I've ranked according to which have the highest revenues. All the revenues above average are highlighted green using Excel 2013's conditional formatting.

What I would like to do is have a forumla where all the green cells are automatically copied into a new sheet and pivot chart but every time new values are added to the original sheet which are above average they are automatically transfered to the pivot table without me having to do it manually.

I've used the formula =OFFSET($A$1,0,0,COUNTA($A:$A),19) to have new data pasted into pivot tables in other sheets in my workbook but this formula dosent work with the green filtered cells. What formula would I have to use to achieve my goal? I've managed to get the green cells to appear in a pivot table I just need to make it update every time values above average are added to the original list.

Many thanks,