INDIRECT Not Working for This

[eashtm01]

Hello Friends,

I feel pretty stupid, but I can't get =INDIRECT to make the following concatenated string work as a formula. Any suggestions? Thanks.

=CONCATENATE("='["&A4&"-"&$C$2&".xlsx]"&LEFT($A$2,3)&"'!$I$3")

 

[Joe4]

You need to surround the range reference you are building with INDIRECT.
Seeing as how I have no idea what you are building looks like because you have not shown us any data, or what the range you are builidng should look like, maybe something like this:

=CONCATENATE(INDIRECT("'["&A4&"-"&$C$2&".xlsx]"&LEFT($A$2,3)&"'!$I$3"))

Also note, that INDIRECT will NOT work on closed workbooks. Your other file must be opened at the time in order for this to work.

 

[jkpieterse]

I suspect it should be as simple as:

=INDIRECT("='["&A4&"-"&$C$2&".xlsx]"&LEFT($A$2,3)&"'!$I$3")

 

[eashtm01]

You need to surround the range reference you are building with INDIRECT.
Seeing as how I have no idea what you are building looks like because you have not shown us any data, or what the range you are builidng should look like, maybe something like this:

=CONCATENATE(INDIRECT("'["&A4&"-"&$C$2&".xlsx]"&LEFT($A$2,3)&"'!$I$3"))

Also note, that INDIRECT will NOT work on closed workbooks. Your other file must be opened at the time in order for this to work.

Joe, thank you for the information. It appears that my problem was the other workbook was not opened. Do you know of a way to accomplish this with the other workbooks closed? I really don't want to have so many files opened at the same time. Thanks again. Tom

 

[Joe4]

No, I do not know of any way to dynamically build the range reference and pull the values from a closed workbook using just formulas.
You may need to take a VBA approach, where you can have the VBA code temporarily open the file to get the results you want/need, and then close the file.