Report: Extract Directories and consolidate in report with categories

RiKi ✭✭✭✭

Hi All, 

Hope all is well. I want to create a directory base extracting information from existing and past projects. In this directory, it will contain 3 columns: 

The main category containing the Cost Code column, the vendor's name, and the sheet name. 

To do that, I have to filter the sheets to only include filenames with the "Directory" word. However, the filtering still includes many sheets without this keyword.   

  1. Is there a way to filter it correctly without having to manually select hundreds of sheets with the filename containing the "Directory" word? There doesn't seem to be an advance filter option when creating reports at Sheet level. 

The intent that I want is:  

Consolidate all vendors from Location X project directories and summarize all consultants based on Cost Code. For example, engineer consultant 1 , engineer consultant 2 will fall under Engineer category based on the Cost Code.   

Appreciate any feedback if you have done a similar exercise. Thanks.