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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,924
Members
414,416
Latest member
Nobu

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