FIll rows new sheet if row old sheet is chosen

Hi guys,

Hope you can help me out! I am trying to autofill a new sheet with rows of chosen rows in another sheet.

For example, lets say you have 100 outlets in a list and you choose 40 of them (By filling a cell behind the row), i want those 40 to be displayed in a new sheet below one another without there being blank rows in between.

Is anything like this possible?


Kind regards,
Martijn

It works as a charm!

However i had an additional question. Since i want the rows to appear on a new sheet, i used an indirect formula to link cell A:A to the sheet name where i pull the data from (Have to copy the same formula over multiple sheets later on). However, when i use a Indirect formula, i can't drag the formula to the right or down because the cell is locked. Would you have any idea how to bypass this with an indirect formula? The formula now looks the following:

{=IFERROR(INDEX(INDIRECT("'"&A2&"'!"&"A:A"),SMALL(IF(INDIRECT("'"&A2&"'!"&"$S$2:$S$100000")="X",ROW(INDIRECT("'"&A2&"'!"&"$A$2:$A$100000"))),ROWS(INDIRECT("'"&A2&"'!"&"$A$1:A1")))),"")}

Help would be much appreciated!

Alright made a very simple table without formulas. Basically i want to make your table from F but then put that data into a new sheet using indirect. Or maybe you know another easy formula to do that with. Any help is appreciated !

I came up with something like:

=IFERROR(INDEX(INDIRECT("'"&A2&"'!"&CELL("address",A:A)),SMALL(IF(INDIRECT("'"&A2&"'!"&CELL("address",$S$2:$S$13))="X",ROW(INDIRECT("'"&A2&"'!"&CELL("address",$B$2:$B$13)))),ROWS(INDIRECT("'"&A2&"'!"&CELL("address",A$1:$B1))))),"")

But doesn't seem to work the way i want it yet!