What I have is 3 sheets named Jan,Feb & Summary. Sheets Jan & Feb have the exact same layout with the column headings as "Name" as A1, "Case Number" as B1, "Pass/Fail" as C1. I have named the ranges as Jan_Name, Jan_Case, Jan_Pass_Fail, Feb_Name, Feb_Case & Feb_Pass_Fail.

On the Summary sheet I have dropdown list in A2 where you can pick a Name which are the same names in the Name columns on Sheets Jan & Feb. When a name is selected from that list, cell B2 has a COUNTIF function which shows the number of times that name appears in the column "Name" in Sheets Jan & Feb and in cell C2 I have another COUNTIF function which shows how many "fails" are listed against that name in both sheets under the "Pass/Fail" column.

What I am struggling with is to display a list of case numbers which match the selected name from the dropdown and are also fails from both Jan & Feb sheets.

I have been looking online for hours and found something that I can get to work for one sheet but don't know where to begin to get the following formula to include both sheets (this code is on the Summary sheet in cell A6):

Please Login or Register  to view this content.
Also, I believe the AGGREGATE function is only available for Excel 2010 onwards but I will need to change this so that it works on 2007.

Any ideas?