Indirect Function with a separate Workbook

[AntMac]

This seems simple which makes it that much more frustrating. I'm trying to do a simple use case of the Indirect function to pull a value from another workbook. I understand all the restrictions about the book needing to be open and because of that I have it open. I'm not sure what's making it not work. My use case is I have a master file which tells me each row of data could be in one of 7 files. And where in the file is the data for that row's information. So, I have formulas to find the file name (column D), sheet name (Column E), and cell (Column H - I even have G and F to get the column and row individually if that would help). I put in the indirect formula as =INDIRECT("'["&D2&"]"&E2&"'!"&H2) and I get error messages. Based on my troubleshooting up to this point the problem seems to be the workbook or the worksheet name. (I've come to this conclustion because when I hard code it and change the cell to H2 it works. When I change either the file name or worksheet I get the #ref error. Both the workbook and worksheet have a space in them. Which might be pointless information. But it might be helpful to the problem.

I'm at a loss.

 

[GlennUK]

I'm guessing that a direct link would contain single quotes ... you don't seem to have any. Show the direct link version of the formula you've shown.

 

[AntMac]

I'm guessing that a direct link would contain single quotes ... you don't seem to have any. Show the direct link version of the formula you've shown.

Sorry for the delay. I see the quotes in there, but maybe because they are adjacent to double quotes is the problem. However when the source file is closed, this is how the "formula" looks.

='https://sharepoint.com/blah/[North America Data.xlsx]USA 2023'!$L100

The way I have my data:
D2 has North America Data
E2 has USA 2023
F2 has L100

I did use the blah, both to simplify and protect company data. But in my use case, the path up to there is hard coded. The dynamic starts at the file name.

 

[GlennUK]

You are showing the link formula when the file is closed ... but I thought you said that all of this is going on with the file open?

 

[AntMac]

Ultimately I would like the link to work when the source file is closed. But I couldn't even get it to work when it was open. I ended up for now using a macro for one worksheet. But I need to do for 3 other worksheets as well. I was hoping to make the formula be dynamic that when used with a table I just put it in the first row and all rows populate. I think the final step on the workaround is just a for loop on the 3 worksheets. Or just a call the sub for each worksheet. But I'm still confused on why the original plan didn't work. So, maybe some boring vent on the workaround vs the original problem.

 

[GlennUK]

INDIRECT does not work with closed workbooks. You may want to explore Power Query for transferring data from workbook to workbook.

 

[AntMac]

INDIRECT does not work with closed workbooks. You may want to explore Power Query for transferring data from workbook to workbook.

Ok that makes sense. Thank you. Glad I did my work around from frustration. Lol