I ran into this problem in accounting. A workbook was being generated by the general ledger that created separate sheets for each ATM, with dates and deposits. There were a total of 19 ATMs. Individual dates for each sheet were in column A and amounts in column C. The dates did not necessarily show up in the same row for each sheet, nor was the same date on all sheets. In addition, the same date may show up more than once on a sheet. A chart was needed showing all deposits in all ATMs for each date.
First, we need a list of all sheets. A small macro did that. Although we only had 19 ATMs, larger institutions could have considerably more and the macro would be a necessity:
Dim wSht As Worksheet
Dim allwShts As Sheets
Set allwShts = Worksheets
For Each wSht In allwShts
ActiveCell.Value = wSht.Name
The list of sheets I named MySheets
The dates :
Enter in cell B4 and copy down the following formula: =SUMPRODUCT(SUMIF(INDIRECT(“‘”&MySheets&”‘!a1:a500”),A4,INDIRECT(“‘”&MySheets&”‘!c1:c500”)))
INDIRECT – This function turns a string into a reference. For example: indirect(“‘”&MySheets&”‘!a1:a500”), refers to the range a1:a500 on the sheet referenced by MySheets. MySheets is a dynamic range of all the sheets to be evaluated.
SUMIF – This function uses the indirect references and sums each sheet based upon the criteria that it match a certain date. However, when used on its own, sumif will only report on the first sheet created. The function works on a range, but not an array.
SUMPRODUCT–This function allows the SUMIF function to be performed on all 19 worksheets, by creating an array of summations for each sheet that is then totaled.
The answers are than charted: