Reference main table to populate a new subset table based on conditions

Wilson Mah · 11/01/19

Hi,

I created a workspace that contains a list of project folders (and its respective sheets, reports and dashboards). I'm looking to produce a new consolidated Master sheet based on multiple criteria from each main reference sheet found in the workspace. In the simplified example attached, the new master table must be populated with all row information based on TaskCount > 0 from every main reference sheet by finding and populating the first instance into row 1, then any subsequent instance into subsequent rows "n". The purpose of the new master sheet is to show only those rows where taskcount > 0 (Resource having tasks) and then using this master sheet to produce another new Master List that eventually tabulates the final totals per person. I'm looking for 1 cell formula to get me started for the first Master sheet (then I can manipulate and update the rest of the cells accordingly including the second Master - final total task count by Resource). The reason for this is that I tried linking all sheets into a first master but the tool is limited to 5000 links using this method (context: each table is 150 rows X 25 columns; times 25+ sheets in the workspace which easily exceeds the 5000 link threshold). This new alternative method removes those rows where count = 0 and hence reduces the overall row usage to something more manageable.

Or if there is another suggestion, it's greatly appreciated.