Combining cell references into formulas

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
138
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?
 

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

Joe4

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

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
138
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can you post an example of EXACTLY what is being returned, and EXACTLY what you want it to look like?
 

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
138
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?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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".
 
Solution

Peteor

Board Regular
Joined
Mar 16, 2018
Messages
138
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.
 

Joe4

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

Watch MrExcel Video

Forum statistics

Threads
1,123,441
Messages
5,601,689
Members
414,467
Latest member
8Ball963

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
Top