• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automatic date change within a file name

S

SEF2107

New Member
I am trying to figure out a way to change the name of a month within a file name. Currently I am using "find and replace" but this can be cumbersome when using large amounts of data. As an example here is a sample of a formula I currently use:


'=SUMPRODUCT(--('M:Aged Care2004 Accounts2011 Accounts[PAC Report June 2011.xlsm]Working'!B:B=B5),'M:Aged Care2004 Accounts2011 Accounts[PAC Report June 2011.xlsm]Working'!F:F)-SUMPRODUCT(--('M:Aged Care2004 Accounts2011 Accounts[PAC Report May 2011.xlsm]Working'!B:B=B5),'M:Aged Care2004 Accounts2011 Accounts[PAC Report May 2011.xlsm]Working'!F:F)'


Is there a way to automatically change June to July and May to June? I thought of using 'Indirect' but this will not work unless the other file is open.I have looked around for an answer but I think it might be my wording of the question that is hindering me as well.
 
SEF2107


Firstly, Welcome to the Chandoo.org Forums


This is a candiadte for Indirect to be used to construct the ranges within the sumproduct


=SUMPRODUCT(--(INDIRECT("'M:Aged Care2004 Accounts2011 Accounts[PAC Report "&TEXT(E1,"mmmm yyyy)&.xlsm]Working'!B:B")=B5),INDIRECT("'M:Aged Care2004 Accounts2011 Accounts[PAC Report "&TEXT(E1,"mmmm yyyy)&.xlsm]Working'!F:F"))-SUMPRODUCT(--(INDIRECT("'M:Aged Care2004 Accounts2011 Accounts[PAC Report "&TEXT(D1,"mmmm yyyy)&.xlsm]Working'!B:B")=B5),INDIRECT("'M:Aged Care2004 Accounts2011 Accounts[PAC Report "&TEXT(D1,"mmmm yyyy)&.xlsm]Working'!F:F"))


Where D1 would be a cell with a Date of May 2011 and E1 would have June 2011
 
You must log in or register to reply here.
Back
Top