Linking of Two Workbooks through Formula.

Sir,

I require your help to resolve data linking problem of two workbook sheet. I want to link data of one workbook sheet to one another workbook sheet, both Workbook kept in as same folder. After linking the data, when i close one of the workbook sheet, the data in other work book is shown error as "REF!". Kindly suggest some formula to link data of both sheet to each other permanently and with the provision of updating automatically in the case where one work book sheet is in close position.

Two workbook are attached for reference.

Always thankful,

Regards
Devendra

INDIRECT definitely does NOT work with closed workbooks.
There are some convoluted workarounds but they're hard to implement. One of them uses an old Macro v4 function EVALUATE, which is still supported in Excel 2013. The file would have to be saved as a macro-enabled workbook with the extension .xlsm or .xlsb.

I attach a Power Query solution to bring your data from closed workbooks into Excel.
For Excel 2013, you can install Power Query (it's built-in for later versions) see:
https://www.myexcelonline.com/blog/install-power-query-with-excel-2013/
https://answers.microsoft.com/en-us...cel-2013/e82f878c-04a6-4a6d-97cd-18dcd94f654f

Now I'm not sure how you want to summarise your data, but you could use a pivot table or just use regular functions.
The query is a bit basic at the moment and assumes all the user sheets have data in the same places and there should only be 'user' sheets in the Data workbook, no other sheets. If it's an avenue you'd like to explore I can make it a bit more flexible.
At the moment it loads all data from all the sheets and attaches the sheet name (user name) to each row; as many as there are sheets in the workbook.
The query looks at the cell E2 in your Summary workbook (which is named range called FullPath). This cell contains a formula concatenating cells C2 & C3. Note that you attached a Data.xlsx file but you had a .xls file name in cell C3. This must be correct for it to work.
If the Data file changes you'll need to refresh the table by right-clicking it and choosing Refresh. If you change the location or name of the data file then you should change C2 and C3 accordingly and refresh the table.

Without using Power Query, if you're prepared to use macros (vba) we can code it to update formulae in cells (without the need for INDIRECT) which will be able to gain access to data in closed workbooks. Is that an option with you?