how can I make a path variable in excel?

Question

Hi,

I am trying to create a summary of laborhours for each week. To do so I created a folder system in which I export the hours of each employee via an external program to a *.xlsx file, for example: C:\Staff\Laborhours\Employees\Employee_A\2023\01\Hours.xlsx. In my main summary file, I have different sheets for each week in which I link the total hours of each employee.

My thought on the approach was to create a cell on each sheet that contains the number of the week, as the variable, for the following forumula: =SUM(INDIRECT("'C:\Staff\Labourhours\Employees\Employee_A\2023"&B1&"[Hours.xlsx]Sheet'!$E$2"))

This way I only have to create one sheet, but for some reason it gives me an error.

Am not an expert on Excel, is there someone who can tell me what am doing wrong here?

Answer 1

Indirect function returns #ref when source file is closed - Microsoft Community

The INDIRECT() will work only as long as the source data file is open.  When the source file is closed, the INDIRECT() function cell will yield an error. 

=SUM(INDIRECT("'C:\Users\Administrator\Desktop"&D2&"[Hours.xlsx]Sheet1'!$A$1:$A$5"))

Answer 2

Thank you all for the quick replies.

Ill take a look at using power queries! From what I can see this will indeed be a better way of making such forms.

Answer 3

There is a much better way for this kind of tasks, use Power Query and import all data from folder, e.g. into a data model.

After that you can apply different methods to analyze the data / show reports.

If you need further help please share at min. 2 of your excel files.

Andreas.

Power Query #08: Import Multiple Excel Sheets From Multiple Excel Workbooks - YouTube

Answer 4

Does this work?

=SUM(INDIRECT("'C:\Staff\Labourhours\Employees\Employee_A\2023"&TEXT(B1,"00")&"[Hours.xlsx]Sheet'!$E$2"))

If you use comma as decimal separator:

=SUM(INDIRECT("'C:\Staff\Labourhours\Employees\Employee_A\2023"&TEXT(B1;"00")&"[Hours.xlsx]Sheet'!$E$2"))

See the reply below - INDIRECT does not work with a closed workbook.