Auto-populate smaller table from larger table

[Takhisis4tw]

Hi,

I have a ridiculously large table with an insane amount of information in it that is required for me to have an overall look at all people requiring work to be completed or not, and all details they have provided that may be relevant for other areas so is required for me to have somewhere.

I have data validation for the table to select the drop down in this table whether or not the work I am focused on is requried to be completed.

My hope was, that using formulas such as =IF or =VLOOKUP or combinations of them and others I could find a way to have my much smaller spreadsheet auto-complete the information needed based on whether that column is YES. It is important to note that there are more than twice as many columns in the larger table as are in the smaller table, as I only require a much smaller amount of the information to complete the work itself.

I would prefer this than filtering and hiding as it will keep the information separate and would just require me or any other person to change that column cell to a Yes or No to update the smaller table.

Both are in the same worksheet, different tabs.

I have been going around in circles playing around and testing different things I have researched on the many many excel forums but have yet to have any luck so would be appreciative of any suggestions from you lovely people. 

(additional info, large table has columns A - S, smaller table only requires info from

B, F, G, N, O, P)

[#2 · Accepted solution]

@Takhisis4tw 

An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

Information of rows that have YES in column H is returned in the green table. If you work with Office 365 or Excel 2021 you can apply the FILTER function. FILTER function updates without having to refresh.