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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

SozDaneron

New Member
Joined
Oct 11, 2016
Messages
3
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
 

SozDaneron

New Member
Joined
Oct 11, 2016
Messages
3
Seems like this can't be done because Excel requires absolute paths when referencing workbooks. VBA seems to be my option.
 

FDibbins

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,682
Messages
5,838,789
Members
430,569
Latest member
sbardelli

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