Problem referencing the first sheet of another workbook with changing names

Good Morning.

I have a problem referencing the first sheet of another workbook.

I download an assortment of CSV files that are then saved as Excel worksheets. They are various sales files, for 3 days, 7 days, 14 days, etc. So, I then have a summary worksheet which looks up the item on each of the sales spreadsheets so that I then have a summary per item with columns of each representing the sales figures over periods of time. The files are saved with names that do not change, but that is not the problem, but rather with the name on the tab of the first worksheet.

The problem is that when the CSV files are downloaded (and I have no control over this) the name of the first sheet is always the date of the download, so that if I download those reports today, the first sheet name is "BusinessReport-7-24-2013", and changes every day. What I have been forced to do is change the sheet name in all of the CSV files when I save them to "a" instead of the date, so that I can use this formula in my summary worksheet:

=SUMIF([Sales7.xlsx]a!$D:$D,A3,[Sales7.xlsx]a!$J:$J)

Similary, in other columns, the formula would reference Sales10.xlsx, Sales14.xlsx, Sales30.xlsx, etc.

I have thought of adding a cell with a formula for today's date and then reference that in the formula, but then the workbook would have errors before the other files are downloaded on that day.

Is there ANY way to reference the first sheet of the other worksheets REGARDLESS of what the name of it is, to avoid having to do this step on every file every single time when saving them?

I thank you in advance for any help.

B.

Maybe this will help. Put this UDF in a module of your summary sheet (google on how to add UDFs if you don't know how).

Please Login or Register  to view this content.

You use this like this:
=FirstSheet("Sales7.xlsx")
it will return the name of the first sheet. Note that the Sales7.xlsx file must be open.

Now, to use it in your formula, you would need to use the INDIRECT function. Something like this:
=SUMIF(INDIRECT("'[Sales7.xlsx]"&FirstSheet("Sales7.xlsx")&"'!$D:$D"),A3,INDIRECT("'[Sales7.xlsx]"&FirstSheet("Sales7.xlsx")&"'!$J:$J"))