Excel populate table from same workbook

Question

I have a table on Sheet1. I want to pull in data into this table based on entry in another tab. For example, I have a table

based on information in other tabs, I want to populate the Score column. Can this be accomplished using SQL query or Powerquery (pseudocode- Select "Score" from other tab where Name = Jack ) I can look for events in VBA when if data is entered in the other tabs it can grab it and paste it in this table but it seems messy. The reason I want to do this is, there are multiple tabs where people can enter their Scores. They cannot enter this in the main tab otherwise I wouldn't have a problem.

Answer 1

Load all datasets from your worksheet to PowerQuery and keep them as connections only (w/o loading to worksheet) except the one that you want to populate with the data in the end. After loading you can merge different datasets using selected columns as the key for joining. The result of the merge would be a table from which you could retrieve particular columns of interest.