Array Formula Cell References

[crookesa]

I have a formula that works but want to make it more dynamic rather than being hard coded.

=IF(ISERROR(INDEX(employee_names,SMALL(IF(employee_1=$I$21,ROW(employee_1)),ROW(1:1))-1,1))," ",INDEX(employee_names,SMALL(IF(employee_1=$I$21,ROW(employee_1)),ROW(1:1))-1,1))

the employee_1, employee_2, etc. range refers to the 1st day of the month to look up the employees shift.

I have cell G1 on each worksheet I have created for the days of the month so day 1,2... which has the respective day in it.

What I am trying to do is if that cell G1 is changed then date is changed and the respective formulas will work based on that cell value.

I have tried using

[I]=CONCAT("employee_",DAY(G1))[/I]

where G1 is the date field to dynamically change the formula but it comes up with an empty array.
I know the employee_1 are named ranges and wonder if that is at all possible. I have also tried to have the CONCAT formula is a helper cell and reference that rather than insert into the formula but still no luck.

 

[steve the fish]

Use INDIRECT eg

INDIRECT("employee_"&DAY(G1))

which will return the actual value of the named range.

 

[crookesa]

Thanks for that, everyday is a learning day

Use INDIRECT eg

INDIRECT("employee_"&DAY(G1))

which will return the actual value of the named range.