Pulling data based on criteria from external workbook

Question

Hi there,

I'm somewhat of a novice to Excel so apologies if I'm not articulating correctly!

For my workplace, I need to create a tracker that can pull data of those participants who have entered work. We must follow up every 3 months.

I have a workbook that has 12 different sheets - each for one month - listing those participants who have signed up with us. I need to pull just their first name, surname & the name of their advisor (columns B, C & I respectively) IF they have an outcome (column L) that is one of multiple criteria (a drop down menu including Traineeship, Employment, Other etc). But on my tracker, I only want it to show me the participants who have one of those criteria in the outcome - not those who are blank.

I'm struggling with the best way to action it, as it's an external workbook & features 12 different tabs for each of the months.

If anyone could give advice on whether I should be using vlookup or some other formula, I would be so grateful!

Answer 1

Yes and yes.

Follow the steps in the video, all data from all files is condensed into one sheet. Or in your version you can load it into a data model and use Power Pivot .-)

At the end of the video he shows that new data (and/or new sheet and/or new files) are also added automatically.

Power Query is a very powerful tool, it is no more complicated than Excel itself. Imagine you are opening Excel for the first time. After a short familiarization period, it quickly becomes easy to select the appropriate commands with the UI.

Don't look close to the generated M-Code, that's for advanced users.

Andreas.

Answer 2

Thanks for the video, I appreciate it.

I have figured out how to add the source data, but I'm not sure how to condense it to one sheet? Will I be able to have a live link to those monthly sheets that are still being added to even if I merge the loaded sheets?

This is definitely more convoluted than I thought it would be (being a novice and all)!

Answer 3

Please watch the video below.

Andreas.

Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube