Get value from 'TEXT STRING' of external file's link in a cell

Dear Sir

I have a separate Excel Workbook for each of our Sales Center. I have to prepare a summary report for Cash Sales. Cash Sales value is derived from D36 of each worksheet.

I don't want to manually D36's value in my Cash Sales report by linking external link! Rather I want to create the link text and want to get value from link text. Is is possible to get the value from Text of external link files?

For your kind consideration I have attached the demo file.

Will you please reply if you have time?


Farid
Dhaka Bangladesh

If I understand what you are trying to do, you are describing what the INDIRECT() function does: https://support.microsoft.com/en-us/...1-92b6306fa261 Unfortunately, your example shows that you are using full path filenames, which implies that you intend for these files to be closed, and INDIRECT() can only work on open files. If you have all of your files opened, then INDIRECT() will work just fine for this, but if you insist on having the files closed, then it won't.

I am not sure exactly how this particular task fits into your larger project, but this seems to be to be something perhaps better accomplished by bringing all of the data into a good database, then using other database tools in Excel to query, pivot, etc. the data.

Are you committed to the approach you have outlined in the OP, or are you willing to consider other ways to compile and query your data files?

Then the INDIRECT() function will not work for you.

Power Query/Get and Transform seems like the next best possibility, but I am not familiar with how it works. This tutorial may be useful: https://www.excel-university.com/ret...any-workbooks/

I know VBA can 1) read text/filename in a cell, 2) Open the file indicated by text, 3) Retrieve data from the file, 4) Close the file, but that still involves opening the file. I suspect that a PQ based solution will be better than a VBA based solution.