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.
="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.
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?
(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:= _
Windows("PKG PROD NUMBERS TEST2.xls").Activate
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.