add file name into a SUMPRODUCT formula

[Rodrigues]

Hi There
Have the following source files name:
2017-05-16 Batch Yield Report 0600-0659
2017-05-16 Batch Yield Report 0700-0759
2017-05-16 Batch Yield Report 0800-0859 and so on.
This file, auto runs every hour, I would like to add this file name into a SUMPRODUCT formula (file attached) starting on cell C10, however rather than have to amend the formula hundreds of times/daily because of the date, wonder if it's possible to pickup the date from cell B10 (as part of the file name) instead of to have to add the full file name on each cell daily .
Please note that, file attached works fine, the only problem is that, have to run the source file manually each hour which is becoming unpractical.

Something like this:

=SUMPRODUCT((--(LEN('C:\PlanAttainment\["B10"Batch_Yield_Report 0600-0659.csv]Batch_Yield_Report7'!$A$4:$A$10000)-(6)=1))

Thanks in advance
Regards
R

 

[PCosta87]

Hi,

You can use INDIRECT to set a reference based on cell values. Something like this:

=SUMPRODUCT(--(LEN(INDIRECT("'C:\PlanAttainment\["&$B10&" Batch_Yield_Report 0600-0659.csv]Batch_Yield_Report7'!$A$4:$A$10000"))-(6)=1))

I believe you will still have one (not so small) problem though... I'm pretty sure you must open the file it is referring to in order for the formula to update/calculate, which you wouldn't have to do if the formula directly referenced a closed workbook.

In any case, I hope this helps

EDIT: I forgot to mention that your dates must be Text and not normal dates. Adding ' before the dates should fix that.

 

[Rodrigues]

On Cell L10 , tried INDIRECT and changed date to text, got #REF!, was expecting same result of cell G10.
On Cell N10 tried to modify adding date before file name, comes up with error (removed =sign).

Cells G,H,I 10, shows results from the old file name Batch_Yield_Report7.csv (also added for comparison)

Would BVA read the source file without opening it?
Could you help, please?
Thanks