Need a workaround because SUMPRODUCT doesn't support wildcards

[Royzer]

We're currently using SUMPRODUCT to update daily cash receipts, but the raw data is always in the same workbook as the formulas. We have over 100 workbooks that are set up like this. We've been using a macro to copy and paste the sheet with the raw data into a specific sheet in each workbook each day, which is being referenced by the formula. We've started having problems using this method, so now I'm trying to change the way they are updated to linking all of them to a file in a central folder. I have successfully linked the formula to the current data file, but every month the name of the data file will change. The naming convention is "MONTH YEAR Deposits per Bank", so I need a workaround to use in the SUMPRODUCT formula so it will connect to the file regardless of the month and year.

The formula pulls in the data based on matches with Company # in column P and the Date in Column A. Thank you.

=SUMPRODUCT((RIGHT('[January 2024 Deposits per BANK.xlsx]Merchant'!$A$8:$C$3000,3)=$P$1)*('[January 2024 Deposits per BANK.xlsx]Merchant'!$A$8:$A$3000=$A9)*('[January 2024 Deposits per BANK.xlsx]Merchant'!$C$8:$C$3000))

Cash sheet-1.png

Cash sheet-2.png

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[Pete_UK]

I think you will find it difficult to get a formula-based solution for this, as you seem to want to generate the filename (as a string) and then use that as a reference in a formula. You would normally do this using the INDIRECT function, but that cannot be used to get data from a file which is closed, and from what you have said I can't imagine that you would arrange to have 100 files open simultaneously.

Probably not what you would want to hear, but hope this helps.

Pete

[DJunqueira]

Depending of the structure of your files you could use Power Query (Excel tool) to import every worksheet that is in a folder, regardless of the name and eventually do some calculations before saving it in your Central Worksheet.

See the video of how can it be done:
Easiest way to COMBINE Multiple Excel Files into ONE (Append data from Folder)

[Royzer]

Thank you, everyone. I will continue to look for other options to get the data into the files.

[Pete_UK]

Why don't you have a temporary file that you copy the latest file into (as well as saving it as "MONTH YEAR Deposits per Bank") and ensure that all previous data is deleted, and then have references to this temporary file so that the filename and sheet name does not change from one month to the next.

Hope this helps.

Pete

[Royzer]

Thank you, Pete. That is an excellent idea!