So I have an excel spreadsheet with 5 different sheets. All five of these sheets have tables detailing Port numbers, devices that the ports are connected to...etc. What I want, is to use the fifth sheet, so that if the column detailing device details has N/A, I want that entire row to appear in the fifth sheet.

So for example, in the image attached, there is one where the device is listed as N/A. The fifth sheet is a table that is identical to this one, except it is blank. Ideally, as that device is listed as N/A, I'd like the entire row to appear in the fifth sheet.

As an added bonus, if possible, I would like it to to keep updating, so if I change N/A to INTEL for example, it automatically removes itself from the fifth sheet. Does anyone have any ideas or suggestions?

So I have an excel spreadsheet with 5 different sheets. All five of these sheets have tables detailing Port numbers, devices that the ports are connected to...etc. What I want, is to use the fifth sheet, so that if the column detailing device details has N/A, I want that entire row to appear in the fifth sheet.

So for example, in the image attached, there is one where the device is listed as N/A. The fifth sheet is a table that is identical to this one, except it is blank. Ideally, as that device is listed as N/A, I'd like the entire row to appear in the fifth sheet.

As an added bonus, if possible, I would like it to to keep updating, so if I change N/A to INTEL for example, it automatically removes itself from the fifth sheet. Does anyone have any ideas or suggestions?

• excel

• Use =FILTER(Sheet1!A1:D5000,Sheet1!B1:B5000="N/A"). If they are true error value then use =FILTER(Sheet1!A1:D5000,IFNA(Sheet1!B1:B5000,TRUE)). – Harun24hr Commented Feb 5 at 9:09

1 Answer 1

You can VSTACK() all your tables, and then use FILTER():

=FILTER(VSTACK(table1,table2,...) , INDEX(VSTACK(table1,table2,...),,2)="N/A", "no N/A")

Table1

Table2

Output

Or slightly easier to maintain:

=LET(_data, VSTACK(table1, table2, ...),
Filter(_data,INDEX(_data,,2)="N/A", "no N/A")

Where 2 would be your column number which could contain N/A.