How to pull information from a closed workbook using cell contents in a formula

[Bri2000]

Hello,

Please forgive me if this was posted elsewhere. I spent most of yesterday trying to find this, but could not.

Anyway, I'm trying to pull information from one of 22 different closed Excel workbooks. The formula would look similar to this: "='[2022 MPS Expenses.xlsm]Jan'!A3". However, I would like the formula to adjust to one of the below formulas based on the value (year) I enter in B6:

"='[2021 MPS Expenses.xlsm]Jan'!A3"
"='[2020 MPS Expenses.xlsm]Jan'!A3"
"='[2019 MPS Expenses.xlsm]Jan'!A3"
"='[2018 MPS Expenses.xlsm]Jan'!A3"
"='[2017 MPS Expenses.xlsm]Jan'!A3" and so on based on the year that I enter in B6.

Is this possible? I would prefer to do this without VBA, but I welcome all suggestions. Thank you all so much for your time.

 

[6StringJazzer]

You are probably going to need VBA one way or the other.

The way to do this is:

=INDIRECT("'[" & B6 & " MPS Expenses.xlsm]Jan'!A3")

However, you will get a #REF! error unless the other workbook is open. Most people are not looking for that kind of solution. The only way around that is to do one of these:

1. Use VBA to write the full formula to your sheet whenever B6 changes
2. Use VBA to open the other workbook, retrieve the value, and put in the cell
3. Use a VBA UDF which will open the other workbook, retrieve the value, and return that value
4. Use a VBA UDF which will use ADO to get the value out of the workbook without opening it, and return that value
5. Use a VBA add-in that will provide a UDF as described above