Dynamic reference in excel

Not applicable

Dynamic reference in excel

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

3 Replies

Re: Dynamic reference in excel

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

Re: Dynamic reference in excel

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).


Re: Dynamic reference in excel

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

Related Conversations
Dynamic Groups Help
Daniel Hudson in Microsoft Intune on
2 Replies
ATP dynamic delivery and 3rd party products
Jonathan Wood in Office 365 on
2 Replies
SharePoint & Dynamics 365
RJ Miller in SharePoint AMA on
2 Replies
Dynamics 365 connector missing
Arne De Lathouwer in Microsoft Teams on
1 Replies
Is there any integration with Dynamics 365?
Zhenyu Wang in Microsoft StaffHub on
1 Replies