Combining cell references into formulas

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
152
I would like to "recreate" another Worksheet from another Workbook on My Worksheet, so I can do things with it. The title of the other worksheet changes everyday to include today's date. I would like to place the following in My Worksheets cell A1, and it always automatically update the date. I think I am missing something. Excel usually updates the filepath, file name, Worksheet, and cell automatically when I close the other file. Any help would be appreciated.

A1 --> = "filepath" & today() & "filepath".xlsx
or
A1 --> = "filepath" & A2 & "filepath".xlsx

What am I missing here in trying to make this work?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
So what exactly is the issue?
What is happening?

Is the date in A1 not being updated?

Note that if you are trying to build a valid file name/path, you cannot have slashes (like from a date) in your file/folder name.
You can use the TEXT function to designate the format of the date portion, i.e.
Excel Formula:
= "filepath" & TEXT(today(),"yyyy-mm-dd") & "filepath" & ".xlsx"

You also need your ".xlsx" to be inside quotes too, and you may need to manually add the slash between the last folder and the file name.
 
Upvote 0
Joe - It absolutely seems you understand what I am trying to do. I just tried what you are saying, but it did not work.

="filepath" & TEXT(TODAY(),"MM-DD-YYYY") & ".xlsx]Sheet1'!$A$1"

It now outputs correctly in the cell as what appears to be the string value, rather than execute the whole as a formula. What do you think?
 
Upvote 0
Can you post an example of EXACTLY what is being returned, and EXACTLY what you want it to look like?
 
Upvote 0
I will try, but it will be transposed to something I can post online. Present within cell A1 is the formula:

="'C:\Users\John.Doe\Desktop\New folder\[Book2_" & TEXT(TODAY(),"MM-DD-YYYY") & ".xlsx]Sheet1'!$A$1"

When this formula executes, rather than go to that file, and output the contents of cell A1 on that file, it is just outputting the following string within Cell A1 (below is how it appears in the cell after execution of the above):

'C:\Users\John.Doe\Desktop\New folder\[Book2_12-17-2020.xlsx]Sheet1'!$A$1

Any ideas?
 
Upvote 0
OK. When you are dynamically building a range reference like that, it does not recognize it is a range reference.
Typically, the way to get around that is to use the INDIRECT function, like this:
Excel Formula:
=INDIRECT("'C:\Users\John.Doe\Desktop\New folder\[Book2_" & TEXT(TODAY(),"MM-DD-YYYY") & ".xlsx]Sheet1'!$A$1")
However, the caveat is that the INDIRECT function only works if the other workbook is open. If it is not, it will not work.

If you want to try to do it on a closed workbook, it gets very tricky. There are different approaches to the issue.
Here is one: VBA to pull data from dynamically named closed workbooks

To find others, simply Google: "Excel using Indirect on Closed workbook".
 
Upvote 0
Solution
Nailed it Joe. Thank you. Looks like as you indicated, a Macro will probably be my easiest route. Another issue I noticed with this method is even though I changed the Absolute cell reference to Relative, it did not change when I tried.
 
Upvote 0
Another issue I noticed with this method is even though I changed the Absolute cell reference to Relative, it did not change when I tried.
Keep in mind that anything found between double-quotes is text, and will not increase/descrease as you move across cells.
I have seen people use OFFSET to account for that.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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