Dynamic reference in excel

[Deleted]

Trying to find a way to reference information across multiple sheets with dynamic information. I have multiple queries running concerning job metrics with employees. I cannot simply reference the names since I also have a dynamic work force (i.e. employees transfer, hired or quit). I get the employees metrics from different sources sent to me by others in the company. So i have loaded a few different queries of other workbooks to get all of the metrics I need. And since all of the metrics change daily, I can just save over the existing queried workbooks and refresh my main spreadsheet.

What I need help with example: First tab is "Overview", second tab is "Metric 1" third tab is "Metric 2", etc.  Cell A2 references all employees in Metric 1 column "A" and then the metrics I need from that query are in the next few columns B:O. Next I want to reference the same employees from Metric 2, but the employees are not listed same. Here is where I am having the problem. How do I now reference the employee in cell A2 to in the Overview tab that is somewhere else in column "a" in the Metric 2 tab and the metrics that are related to him in the subsequent columns? Thanks

[#2]

Created a simplified workbook of what I'm trying to do.  

[#3]

Hi James,

It depends on how your real data is organized, but in general INDEX/MATCH combination shall work. If you keep your metrics in tables like Metric1, Metric2, etc. you may find in each of such table the record (row) where employee name matches (MATCH) and return the value from the needed column for such row (INDEX) like

=INDEX(Metric1[Production Job Count],MATCH([@Employee],Metric1[Employee],0))

Please see attached file.

These could be ranges, not necessary tables. Another way is to use PowerQuery (Get&Transform).

[#4]

Great, I will give it a try!  Thanks for the response.