CODEWITHSUNDEEP
excelexcel-formula
Guyj91
Guyj91

Reputation: 39

Excel Reference another sheet from within a function defined by a cell

Ok hopefully i can explain what i am needing to happen. So i have an Excel Document that keeps track of our staff members individual KPI's that is stored in their own sheet. What i am wanting to do is create a sheet that acts as the template for the monthly overview. So that when i enter the month into cell B3 and the staff members name into cell E3 it will goto the staff members sheet and get the required information from there.

Here is the formula i have so far that works well:

=INDEX('Jack'!F5:M84,MATCH(B3,'Jack'!A5:A84,0),MATCH(A6,'Jack'!F3:M3,0))

In the code above 'Jack' is the name of the staff members sheet - B3 is the month to search for - and A6 refers to the KPI i am looking for. I need to replace 'Jack' with the text that is entered into E3 so that i don't need to change the code for every staff member.

Any help is greatly appreciated.

Upvotes: 0

Views: 61

Answers (1)

Harun24hr
Harun24hr

Reputation: 36750

Try this formula. May be it will work for you.

=INDEX(INDIRECT(E3&"!F5:M84"),MATCH(B3,INDIRECT(E3&"!A5:A84"),0),MATCH(A6,INDIRECT(E3&"!F3:M3"),0))

Upvotes: 1

Related Questions