Only Show data that matches criteria

[Charlie12345]

Hello

I have a spreadsheet where from a list of 4000 customers I need to bring the details of about 150 to another page for further analysis. I have attached a sample sheet to show what I want with the "data" sheet representing the details of the 4000 customers and the "Main" one representing the 150 in which I'm interested. All I want to show in "Main" A2, A3, etc is the account numbers from "Data" A2, A3, A4 etc if "data" B2, B3, B4 etc says "TRUE" next to the relevant cell - in my example returning the data I've shown in cells "Main" F2, F3 and F4.

The data in the "data" sheet is coming straight from live SQL database and it would be great if the rows on the "Main" sheet could automatically update if relevant rows are added to the table on the "data" sheet.

[Pete_UK]

Put this formula in D2 of the Data sheet:

=IF(Table1[[#This Row],[Include]],MAX(D$1:D1)+1,"")

then copy down to the bottom of your table. Then put this formula in A2 of the Main sheet:

=IFERROR(INDEX(Data!A:A,MATCH(ROWS($1:1),Data!D:D,0)),"")

then copy this down.

Hope this helps.

Pete

[Charlie12345]

Thank you - that works. Only slight problem is that Excel doesn't seem to like the amount of thinking it has to do when applied to my live data...

[BB1972]

Charlie,

Using specific ranges instead of complete columns should speed things up a bit for you, so if you have approx 4,000 customers, I would amend Pete's formula to:

=IFERROR(INDEX(Data!A1:A5000,MATCH(ROWS($1:1),Data!D1:D5000,0)),"")

with the 5000 (or other number of your choosing) giving you some headroom over and above your approx 4,000 customers.