How to use variable cells referrals to link them between different workbooks (variable: worksheet name)

Question

I need to use variable referrals to link data between two different workbooks. My scope is wage calculations for more employees.

One workbook contains the data (working hours split in different columns, e.g. overtime hours, nightshift hours, weekend hours, etc. for different rows that are the single days). Let's call it WORKING HOURS.

The other workbook have to use these data to make the calculations. Let's call it WAGES.

Both of these workbooks contain different worksheets, each one named with the employee's full name.

Now, I need to link the total values (e.g. total working hours) in the cells inside the WORKING HOURS to the empty cells of WAGES, but in a way that is variable depending on the name of the worksheet (that is, the full name of the specific employee).

Is it something that could be done only by using Macro/code or is there a way to put variables in the formula {='[WORKING HOURS.xls]JOHN SMITH'!$E$38} where replacing the part of "JOHN SMITH" with, let's say, active worksheet name code string?

Thanks in advance

Answer 1

E.g.

=INDIRECT("'[WORKING HOURS.xls]" & $B$5 & "'!E38")

where:

B5 is the cell which contains the active sheet name (outputted with a formula like the following one) =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

E38 is the cell which contains the total working hours