Reference a filename created with a formula

KHaag

New Member
Joined
Jun 1, 2018
Messages
14
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.
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

KHaag

New Member
Joined
Jun 1, 2018
Messages
14
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,457
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 1, 2018
Messages
14
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.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,457
Office Version
365
Platform
Windows
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

New Member
Joined
Jun 1, 2018
Messages
14
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,457
Office Version
365
Platform
Windows
Can you post the code you have for opening the file, and we can help you clean it up?
 

KHaag

New Member
Joined
Jun 1, 2018
Messages
14
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,548
Messages
5,469,360
Members
406,647
Latest member
ssinovec

This Week's Hot Topics

Top