Changing date while referencing closed workbook

[SozDaneron]

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

 

[SozDaneron]

Hi Everyone,

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

=SUMIF('Lath\[filename - "&TEXT(WORKDAY(TODAY(),-1),"yyyymmdd")&".xlsx]Sheet'!$BF:$BF,"criteria",'Lath\[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]

Seems like this can't be done because Excel requires absolute paths when referencing workbooks. VBA seems to be my option.

 

[FDibbins]

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