1 Data with 2 rows

[lja1]

Hi, I've been working on a big spreadsheet for a long time and not sure what's the best way to solve this. Basically I multiple workbook with many worksheets inside and each cell consist a lot of formula. My goal is to make a master workbook that contains all of the workbook summary in example workbook2sheet and workbook1sheet. I want the master sheet to be able to filter the name and also title1. Is it possible to have a 2 rows filter per data? And I use the paste link option to copy paste the data but its showing zeros on mastersheet. I want the master to be able to link. Thank you in advance!

 

[jdellasala]

This is a job for Power Query. Hopefully both of the target workbooks are in the same folder or below it. With Power Query you can pull in a list all the files in a specific folder and subfolders, filter for just the files you need, clean their data, append each workbook's data to a single table and bring it in. It would take about the same amount of time to do this manually ONCE, but once you do it with Power Query, it can be repeated when new files are added to the folder with just a click. If you don't know Power Query, it has a great UI, and once the process is set up can be repeated when needed.
There are great playlists on YouTube on the subject here and here. The second link is particularly good as it provides before and after workbooks which are helpful to follow with the video.
Don't be overwhelmed. You should be able to pick up enough to get started after a few videos, and there's always help here (just please use XL2BB when posting data).