Extracting data from several sheets in one workbook

[Glacombe]

I was wondering if its possible to extract data from several worksheet of a workbook into a document I am creating? For example, we have a workbook that contains 11 worksheets and would like a formula that looks into all of them in order to input the info I would need between Nov 2020 to now in this new document. I know its possible to look into only one sheet, as you insert the name of the sheet in your formula, but not sure if you can do it with several.

 

[Logit]

Excel formula: Search multiple worksheets for value | Exceljet

How to search for value in all tabs in Excel

 

[Glacombe]

I have 6 columns with the following text: "Ready for advancement", Ready for lateral movement", "well-placed in role", Develop in role", move to more suitable position", "transition to retirement". Under these columns, in row 1, I have the names of the people that correspondent to one of these action. I have separate document for each of the year and what I would like to have is a formula to get the information into one document. This new document has the name of the people in column A and then it follows in the next columns year 2016, 2017, 2018, 2019 and 2020.

 

[Logit]

It would be best to post your workbook (no confidential information) to a download site, for review.

 

[Glacombe]

Here is a sample of my new document, my list has 136 people, but only gave a few for an example.

Name	2016 Placement	2017 Placement	2018 Placement	2019 Placement	2020 Placement
Doe, John
Doe, Jane
Alvin, Marc
Arson, Jill
Gates, Bill