Retrieve data from another Excel file

[IronPhoenix]

What is a working version of this?

=INDIRECT("'"&Common!$N$5&$B4&"_file.xlsx'!XYZ")

Common!N5 has the relative path of the file to read. B4 has part of the filename, and XYZ is a named cell inside the file indicated. This gets a "#REF!" error.

It still gets a #REF! error when all variables are removed and the entire relative pathname is specified directly: =INDIRECT("'Path\ToFile\ABC_file.xlsx'!XYZ")

However, if I use an absolute pathname (starting at the drive letter - like 'D:\Direct\Path\ToFile\ABC_file.xlsx'!XYZ ) instead of the relative path method shown above, it works, both with specifying the full name directly or in the initial format where parts of the pathname are in other cells. But it only works if the target file is also open.

To be clear, the file identified in the formula is not the file that contains the formula.

How do I get this to work without absolute pathnames (the data exists on different networks where the path is not the same) and without opening the hundred or so files I want to reference like this every time I open the parent workbook?

I'd prefer to avoid PowerQuery, but if that's the only answer, please be very explicit on how to apply it. Kindly assume I know nothing of it (which is the case).

Thank you!

 

[rondeondo]

Hi IronPhoenix,

a macro could be written to systematically open each file in a given list of files and the relevant data copied into your main sheet. Though I suspect you will still need to fill in the full path, is the reason for using relative path due to the full path being variable? There is a handy function, CELL() that can tell you the path of your currently open file, this could be a potential solution depending on the specifics of how the path varies.
I have made a few macros in my time that walk through a folder and open each file within and select all available data or data on a particular sheet to make a file with the contents of all.

Also, yes power query might be the solution that works better, to direct you more specifically would require a bit more info about what you're bringing back from each of the files of interest and how much variablity is involved.

 

[IronPhoenix]

Good morning @rondeondo !

Yes, the reason for using a relative path is due to the absolute path being variable, though the subtree is the same at any location..

There is not a lot of variability in play. The name and location of the subfile to extract data from (always the same except for 3 characters defined in col B of the main sheet), the absolute path, and the name of the item to retrieve from the subfile (3 of them per subfile, all subfiles have the same 3) are the only varying items

Various facilities send me the small xlsx files that have the data I would like to import into the main sheet. Based on your comment, I added a cell (using the CELL function mentioned) to obtain the current location of the main file, and that cell is named "PATH_TO_MAIN", to make it easy to access everywhere without needing sheet names or cell A1-type notation. I already had the subpath ("HL_MDM_Pref") and the remainder of the filename ("HL_MDM_Suff").

Col B of the main sheet is always 3 chars. In this example, "ADA". The filename of the related file always starts with those three characters, unique to that line, and the relative path and rest of the name are always the same. Each "subfile" has three (for now - I might need to add up to three more later) values of interest. These values are (should be!) always in the same cell position, but, in case that changed, I gave them cell names (specifically "MDM", "DEC", "DCP"). These names are the same in each subfile.

This works, IF the subfile is open in Excel somewhere (B36="ADA" and AP2-AR2="MDM", "DEC" and "DCP" respectively):
=INDIRECT("'"&PATH_TO_MAIN&HL_MDM_Pref&$B36&HL_MDM_Suff&"'!"&AP$2)

Having a hundred subfiles open isn't exactly feasible...

 

[IronPhoenix]

I have a (hopefully temporary) solution, which I consider a "kludge". I am importing it in hidden columns, which are values when the subfile is open, and errors when it is closed. The visible columns use IFERROR to retrieve the data from these hidden columns when the subfile is open, but keeps itself (value grabbed earlier when the subfile was open before) when there's an error. This does require setting "Enable iterative calculation"" (File - Options - Formulas) to avoid the circular reference issue... But it does work. I'd rather do it cleanly, though...