Reference a filename created with a formula

KHaag

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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)?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Can you post the code you have for opening the file, and we can help you clean it up?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top