Say I have a table A2:H8 (SourceTable) in my source sheet, as shown in my screenshot_1.

I am trying to create a dynamically updatable table on a separate sheet (named ‘Dashboard’). By ‘dynamically updatable table,’ I mean the one that should expand (more rows) or contract (fewer rows) automatically, based on the number of rows with the value = “report” in the SourceTable.

So if I have 4 rows with the value “report” in H column of my Source Table, the dynamically updatable table on a Dashboard sheet should also have 4 rows. If I switch one value from “hide” to “report” in H column of my SourceTable, the dynamically updatable table on a Dashboard sheet should also now have 5 rows with the respective data.

I managed to do this with the FILTER formula [=FILTER(SourceTable,SourceTable[Status]=”report”)]. See screenshot_2.

BUT!!!

For the added row, I don’t want to copy values from all columns in SourceTable. Say I just need Column 1, Column 6, and Column 7. How do I do that?

This is what I ultimately want (see screenshot_3)