External Workbook Link with Dynamic Cell Reference

I currently have a file that is linked to an external workbook that is referencing a certain cell from a certain tab. The below is what Column A & B look like right now. What I'm wanting to know is is there a way to have the formula in Column B automatically reference the name in Column A (which is the tab name in the external workbook) instead of having to manually change the formula when the value in Column A changes?
In other words, I want everything in the external reference formula to remain the same except have the person's name change dynamically...

Thanks in advance for any help!!! : )

Mary	='X:\Anderson Corporation\Employees\[Work Hours]Mary'!$F$42
Joe	='X:\Anderson Corporation\Employees\[Work Hours]Joe'!$F$42
Frank	='X:\Anderson Corporation\Employees\[Work Hours]Frank'!$F$42
Michelle	='X:\Anderson Corporation\Employees\[Work Hours]Michelle'!$F$42
Sam	='X:\Anderson Corporation\Employees\[Work Hours]Sam'!$F$42