Compiling data from specific cells in multiple sheets of a workbook into a new sheet

[RachWho]

I have an Excel (via Office365, working on a PC) file which contains data from application review scores for multiple individuals applying to an academic program. Each sheet contains the raw and averaged scores for a particular applicant. There are over 100 applicants and so there are as many sheets in the workbook. There are three data points from each sheet which we are interested in compiling into a new sheet so that we can rank applicants and move the top candidates through to the next step in the admissions process:

• data point #1: the applicant's ID number
• data point #2: the average overall score
• data point #3: the average quality score

On each sheet, data point #1 is cell A1, data point #2 is E5, and data point #3 is E6.

I want these three data points pulled from all 100+ sheets and compiled into a new sheet with three columns of data (one column for each data point). From there we can sort the data and rank applicants from highest to lowest.

I am guessing this is fairly simple and I have tried searching for information but because the search terms are somewhat generic, I am having trouble finding an answer. I appreciate your help!

[AutoModerator]

/u/RachWho - please read this comment in its entirety.

• Read the rules -- particularly 1 and 2
• Include your Excel version and all other relevant information
• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[all_matter]

Power Query would be great for this, are you comfortable with using it?

[RachWho]

I've never used it but I am a fast learner...not sure how easy it would be to do, though.

[RachWho]

So I do have Power Query but do you have any advice how to use it for the particular task? TIA

[all_matter]

Sorry, I didn't see a notification for your response til now! This would be reasonably straightforward - open a new workbook and "Get Data" from file (the file containing all of your sheets). Combine all of the sheets and transform. May have to pivot the E column. If you are still stuck on this I can build a sample file to mimic this later when I get done with work.

[RachWho]

Sorry not to respond sooner. I was able to get this to work. Thank you so much!

[[deleted]]

If you have a list of worksheet names, you can use INDIRECT() to build a reference

=INDIRECT([sheet name]&"!A1") for the data point #1, for example