Reference a filename created with a formula

[KHaag]

I'm trying to reference a filename and path that I've created with a formula. I can't get it to work either in a formula or with a Macro.

 

[Joe4]

For what purpose?
How/where exactly are you trying to reference it?

Perhaps you are looking for the INDIRECT formula.
See: https://exceljet.net/formula/dynamic-workbook-reference

 

[KHaag]

I've tried the INDIRECT formula. It gives me back the "formula", it displays the FORMULA complete with the equal sign, but not the formula result value. I tried to do Text To Columns to convert that response to a "working" formula, but that did nothing.

What I'm trying to do is to reference a file that contains information needed in the report that I am working in. The file that I am trying to access has a filename based on a reference date.

 

[Joe4]

Will you have the file you are referencing open or close at the time you are running this?
What does the formula you built look like (please post it here)?

 

[KHaag]

The formula that "builds" the filename is:

="P:\Finance\Bill of Materials\[Routings "&YEAR(Sheet1!$A$2)&"]CS "&YEAR(Sheet1$A$2)& Routings.xls" - This returns the correct filename that I am looking for.

I don't have a copy of the original INDIRECT formula that I used, but when I enter =INDIRECT(A1&"!"&B1) where cell A1 contains the formula above and cell B1 contains the Cell reference that I want, I get a #REF error.

 

[KHaag]

The file that I am referencing is NOT open.

 

[Joe4]

The file that I am referencing is NOT open.

OK. That is the problem. INDIRECT does not work on closed workbooks.

There is an Excel AddIn called MoreFunc that has an INDIRECT.EXT function that will allow you to do this.
See: http://www.ashishmathur.com/tag/indirect-ext/

 

[KHaag]

Thank you, I will look for that Add-in.

The more I'm working on this project, I'm thinking that I'd really like to be able to open the referenced file in a Macro. I'm not having much luck with that, though. Can you help me? How can I reference that filename in the Workbooks.Open command and not get a debug error?

 

[Joe4]

Can you post the code you have for opening the file, and we can help you clean it up?

 

[KHaag]

This is the code I have:

(Sorry, I can't figure out how to get a "Paste" block to copy the code into)

[Dim PeriodBook As Workbook
Dim ThisYear As Integer
PeriodBook = Range("A2").Value
ThisYear = Range("B2").Value
Workbooks.Open Filename:= _
"PeriodBook"
Cells.Find(What:="ThisYear").Activate
ActiveCell.Offset(1,12).Select
Selection.Copy
Windows("PKG PROD NUMBERS TEST2.xls").Activate
Range("A4")Select
ActiveSheet.Paste]

Cell A2 contains the filename P:\Finance\[PERIOD END DATES 2002-2020.xls]Period Dates
this "value" is generated by a formula.
Cell B2 contains the year 2019, again the value is determined by a formula.

I am also having trouble with the Find command in this code.

Thank you.