Avoid Using Indirect Function to Refer to Other Workbooks

[john12345]

Hello,


I want to refer to a cell in another workbook and I need to use a string value from a cell in the current workbook as part of the name of the Workbook that I want to refer to (so that I can use "Data Validation - List" to quickly change the input. I know it can be done by using INDIRECT, but the problem for that method is that the referred Workbook has to be opened, which is not convenient.


For example a formula looks like this, but I want to repalce "WB1" by a cell value from a drop-down list in a cell.
=[WB1_Summary.xlsx]Sheet1!$A$1

I wonder if there is another way to realize this function. Of course VBA will always work but usign VBA is not a good solution in this case since the data is large and messy.

Thank you all very much!

 

[Joe4]

Due to the fact that you are building the filename from a value in a cell, I think you have two options:
1. Use INDIRECT
2. Use VBA

I am not aware of any other options.

 

[john12345]

Okay. Thank you very much.