Hi All
I am trying to create a formula that contains some kind of dynamic range within it to vary where it looks for data (without VBA):
I have a list of employees, with which department they work in:
Employee Name Employee Department
Employee # 1 Department 1
Employee # 2 Department 2
Employee # 3 Department 3
I then want to create a formula in a subsequent column that will go and retrieve a number from the spreadsheet which contains the KPI's for that department e.g.
Department 1 KPI's
Department 2 KPI's
Department 3 KPI's
For example if I wanted to look up cell D5 of Employee 1's department it would be
='Department 1 KPI''s'!D5
What I need to be able to do is create a formula that will insert a referenced department name in place of what I now have a static reference "Department 1" from within the expression.
There will be dozens of departments, with actual department names and there is potential for employees to change department so I need it to be a truly variable expression please.
Thanks
Mike
I am trying to create a formula that contains some kind of dynamic range within it to vary where it looks for data (without VBA):
I have a list of employees, with which department they work in:
Employee Name Employee Department
Employee # 1 Department 1
Employee # 2 Department 2
Employee # 3 Department 3
I then want to create a formula in a subsequent column that will go and retrieve a number from the spreadsheet which contains the KPI's for that department e.g.
Department 1 KPI's
Department 2 KPI's
Department 3 KPI's
For example if I wanted to look up cell D5 of Employee 1's department it would be
='Department 1 KPI''s'!D5
What I need to be able to do is create a formula that will insert a referenced department name in place of what I now have a static reference "Department 1" from within the expression.
There will be dozens of departments, with actual department names and there is potential for employees to change department so I need it to be a truly variable expression please.
Thanks
Mike