Forum Discussion
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 nam...
Created a simplified workbook of what I'm trying to do.
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).
Great, I will give it a try! Thanks for the response.