Changing date while referencing closed workbook

SozDaneron

New Member
Joined
Oct 11, 2016
Messages
3
Hi Everyone,

I am trying to reference a closed workbook that is updated daily. Below is my formula

`=SUMIF('L:Path\[filename - "&TEXT(WORKDAY(TODAY(),-1)),"yyyymmdd")&".xlsx]Sheet'!$DX:$DX, filename - "&TEXT(WORKDAY(TODAY()-1)),"yyyymmdd")&".xlsx]Sheet'!$BF:$BF,"criteria")`

Will the TEXT function work the way I have it? I am able to format a single cell in Excel which leads me to believe that it should work. To clarify, the filename has spaces in it (ie: [This is the file name - yyyymmdd.xlsx]

First post on here!

Thanks,
SozD
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi Everyone,

Just to clarify, I was making a basic mistake above. The "correct" formula should be,

=SUMIF('L:path\[filename - "&TEXT(WORKDAY(TODAY(),-1),"yyyymmdd")&".xlsx]Sheet'!$BF:$BF,"criteria",'L:path\[filename - "&TEXT(WORKDAY(TODAY(),-1),"yyyymmdd")&".xlsx]Sheet'!$DX:$DX)

I am now getting a #VALUE! error and when I formula check, the paths are leading to #REF errors. Could this have something to do with the date or is it even simpler?

Cheers,
SozD
 
Upvote 0
Seems like this can't be done because Excel requires absolute paths when referencing workbooks. VBA seems to be my option.
 
Upvote 0
Hi Everyone,

=SUMIF('L:Path\[filename - "&TEXT(WORKDAY(TODAY(),-1)),"yyyymmdd")&".xlsx]Sheet'!$DX:$DX, filename - "&TEXT(WORKDAY(TODAY()-1)),"yyyymmdd")&".xlsx]Sheet'!$BF:$BF,"criteria")`

Will the TEXT function work the way I have it? I am able to format a single cell in Excel which leads me to believe that it should work. To clarify, the filename has spaces in it (ie: [This is the file name - yyyymmdd.xlsx]

First post on here!

Thanks,
SozD
No, TEXT will not work that way. The way you include text into a formula is by using INDIRECT, but that only works on open workbooks. Perhaps try the Morefunc add-in will allows INDIRECT to use closed workbooks
MOREFUNC | Ashish Mathur's Blog
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

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