Replacing part of a formula with the contents of another cell

[Jak7217]

Hey All:

I currently have a formula as follows:
='[Filename - 6-11-2019.XLSX]Page1_1'!$B$4
This is pulling a piece of data from another excel workbook.
What I would like it to do is remove the 6-11-2019 frombeing hard keyed and have it pull the date from another cell in the workbook.
What I would envision:

='[Filename – “B2”.XLSX]Page1_1'!$B$4
Where B2 = 6-11-2019
The problem is that I am not familiar with what characters Ineed to place around the “B2” to actually get it to link to another cell.
Thanks in advance,

Justin

 

[Joe4]

You need to build the reference, combining literal text strings (enclosed in double-quotes) with cells (outside of quotes), using & or CONCATENATE.
However, that would just build a string. You need to use the INDIRECT function to tell it is a range reference that you are building, i.e.

=INDIRECT("[COLOR=#000000][FONT=Calibri]'[Filename - " [/FONT][/COLOR][COLOR=#0000ff][FONT=Calibri]& B2 &[/FONT][/COLOR][COLOR=#000000][FONT=Calibri] ".XLSX]Page1_1'!$B$4")[/FONT][/COLOR]

Note that if B2 is an actual date, and not a string, you will need to change the part in blue above to:

[COLOR=#0000ff]& TEXT(B2,"m-d-yyyy") &[/COLOR]

 

[Jak7217]

Thank you! This worked perfectly.
I ran into another problem after implementing this. I copiedand pasted the formula over to the next day and am getting a #Ref ! error. Ithink the issue is that excel can’t figure out the file path. The reports forexample are all saved at:
C:\Users\Username\Documents\Reports
In the data tab, clicking on edit links, I see that thesource of my data is linked specifically to the 6-11-2019 workbook, rather thanjust going to the Reports folder and having the indirect formula define theworkbook.
Any thoughts?

 

[Joe4]

I ran into another problem after implementing this. I copiedand pasted the formula over to the next day and am getting a #Ref ! error.

Does that other file exist yet?
Is it open in the same Excel session?
When you have no file path in front of your file name in a formula, I think the formula will only work if that file is already open in the same Excel session.

 

[Jak7217]

Hi Joe:

Good observation - it only works when the excel file is open. I am tinkering around with putting the entire filepath in, but can't seem to get it to stick.

=INDIRECT("'C:\Users\Username\Documents\Reports[Filename - " & B2 & ".XLSX]Page1_1'!$B$4")

 

[Joe4]

You probably need a backslash after the word "Reports".

Here is how I typically approach a problem like this:
1. Create a hard-coded example of the formula I want to create (and make sure it works!)
2. Then try to recreate the exact text via formulas, i.e.

=[COLOR=#333333]"[/COLOR][COLOR=#000000][FONT=Calibri]'[SIZE=3][COLOR=#000000]C:\Users\Username\Documents\Reports\[/COLOR][/SIZE][Filename - " [/FONT][/COLOR][COLOR=#0000ff][FONT=Calibri]& B2 &[/FONT][/COLOR][COLOR=#000000][FONT=Calibri] ".XLSX]Page1_1'!$B$4"[/FONT][/COLOR]

3. Once I get that part to return EXACTLY what my successfully working hard-coded example looks like, surround that formula in the "INDIRECT" function.