excel formula to look to file name with date

[Rodrigues]

Hi. I'm not sure how to explain this, I need some help please, I have a master file where I'm using below formula, which is extracting data from other files.
The problem is that, one of the source files are saved with same name but with current date for each production day ddmmyy.
I would like to have a below formula where looks for file name (full description) but without having to type the date.

=SUMPRODUCT(--('C:\Users\user\Desktop\[Prod Log Days 120615.xls]A - M'!$A$4:$A$10000=1),'C:\Users\user\Desktop\[Prod Log Days 120615.xls]A - M'!$I$4:$I$10000)
=SUMPRODUCT(--('C:\Users\user\Desktop\[Prod Log Days 130615.xls]A - M'!$A$4:$A$10000=1),'C:\Users\user\Desktop\[Prod Log Days 130615.xls]A - M'!$I$4:$I$10000)
=SUMPRODUCT(--('C:\Users\user\Desktop\[Prod Log Days 140615.xls]A - M'!$A$4:$A$10000=1),'C:\Users\user\Desktop\[Prod Log Days 140615.xls]A - M'!$I$4:$I$10000)

Example of full file name:

Production Log Days 100615
Production Log Days 110615
Production Log Days 120615

So at the moment I have to have amaster file for each date, which is not ideal.

Regards
Rodrigues

 

[Hui]

=SUMPRODUCT(--(Indirect("'C:\Users\user\Desktop\[Prod Log Days "&A1&".xls]A - M'!$A$4:$A$10000")=1),Indirect("'C:\Users\user\Desktop\[Prod Log Days "&A1&".xls]A - M'!$I$4:$I$10000"))

Where A1 has 120615

 

[Rodrigues]

Hi Hui
Returns #REF!
Can you please tell me how to fix his error?
Thanks

 

[NARAYANK991]

Hi Rodrigues ,

There is one issue with the formula ; the file name is not the same in the two places that it has been used ; in the second instance an additional space is required in the text Prod Log Days , after the last s of the word Days ; thus , the corrected formula would be :

=SUMPRODUCT(--(INDIRECT("'C:\Users\user\Desktop\[Prod Log Days " & A1 & ".xls]A - M'!$A$4:$A$10000")=1),INDIRECT("'C:\Users\user\Desktop\[Prod Log Days " & A1 & ".xls]A - M'!$I$4:$I$10000"))

However , even after this correction , the formula will display the #REF! error value if the relevant file which is referred to in the formula is not open.

Narayan

 

[Rodrigues]

Hi Narayan
Unfortunately still receiving the same error. Any suggestions?
Thanks
Rodrigues

 

[NARAYANK991]

Hi Rodrigues ,

Please upload your 2 files , one where you use the formula , and the other the data file which is being referred to in the formula.

Narayan

 

[Jake Collins]

Is the problem that indirect doesn't work with closed files?

 

[Rodrigues]

Hi Rodrigues ,

Please upload your 2 files , one where you use the formula , and the other the data file which is being referred to in the formula.

Narayan

Hi Narayan
Please find attached files.
One is called Master, which open daily and do "save as" today date(I.e: 150615 ; 160615 and so on).
Other called Prod Log Days 150615 (todays date).
When I save the master for each day, I would like the formula to pick up automatically the Prod Log Days "date" for the day.
On Master file, as you can see on yellow column, I have some hard code to pick up the file name & date, where is #Ref!, it's where I past the formula.
Tested open first the Prod Log Days and then the Master file but still get the same error.
Your help is much appreciated.
Thanks again.

 

[NARAYANK991]

Hi Rodrigues ,

See the two files now. I have defined two named ranges called :

BasePath , CurrentDate

both of which need to be manually set. Once you open the file , you will need to change the definitions in the Refers To box of both of the above named ranges.

If you want , you can change the manually entered date in CurrentDate to the following formula :

=TEXT(TODAY(),"ddmmyy")

This will then automatically change the definition every day.

Narayan

 

[Rodrigues]

Hi Rodrigues ,

See the two files now. I have defined two named ranges called :

BasePath , CurrentDate

both of which need to be manually set. Once you open the file , you will need to change the definitions in the Refers To box of both of the above named ranges.

If you want , you can change the manually entered date in CurrentDate to the following formula :

=TEXT(TODAY(),"ddmmyy")

This will then automatically change the definition every day.

Narayan

Hi Narayan
Does work many thanks for your help.
Regards

 

[Rodrigues]

Hi Narayan
Does work many thanks for your help.
Regards

Hi Narayan
Apologies for my query again.
I managed to make it work to show totals, but I can't for each individual line.
have attached two files, could you could help me please?
Many thanks in advance.
Rodrigues

 

[Rodrigues]

Narayan
Please ignore my previous email, does work, I had a senior moment here.
Thanks to all for your help.
Regards