How do I gather data from the same collumn in multiple worksheets in a single workbook?

Question

Okay so here is what I want to accomplish. For this example I have a single workbook composed of 4 data sheets plus a totals sheet. Each of the 4 data sheets has a similar name following the same pattern where the only difference is the date. (Ex. 9854978_1009_US.txt, where 1009 is the date that changes while the rest of the file name is the same). In each of those documents column F contains a series of number that I would like to find the sum of but I will have no idea how many cells in F actually contain numbers. (However there will never be additional information below it the numbers so I could in theory just add the entire F column together). I will also add new files to the workbook over time and do not want to have to rewrite the code of which I gather my data from column F. Essentially what I would like to accomplish is for the 'totals' document to take every column F from documents in the workbook with the name of '9854978_????_US.txt', where the question marks change based on the file name. How would I go about doing this in pure Excel code?

Answer 1

In cases like this I would recommend to use VBA, but I've found a (admittedly inefficient) solution with only excel formulas.

In the cell A1 of a new sheet write 1, then 2 in the cell below. Select the two cells and drag down. The autocomplete function will produce the number from 1 until the end of the drag. Carry on until you reach the number 9999

In the cell B1 put the formula =TEXT(A1,"000#"); drag the formula down to get the text from 0001 to 9999.

In the cell C1 put the formula ="9854978_"&B1&"_US.txt"; drag the formula down to get the text from 9854978_0001_US.txt to 9854978_9999_US.txt

In the cell D1 put the formula: =if(ISERROR(SUM(INDIRECT(C1&"!F:F"))),0,SUM(INDIRECT(C1&"!F:F"))) ; drag the formula down to get the sums.

The solution could become much less inefficient reducing the set of possible numbers. Missing the year the possible dates are only 365.

PS: the formulas could be slightly wrong because I translated them from my localized version of excel.

Answer 2

Here's a function that will do this calculation:

Function TotalColF() As Double

    Dim wks As Worksheet

    For Each wks In ThisWorkbook.Worksheets
        If left(wks.name,7) = "9854978" then
            TotalColF = TotalColF + Application.WorksheetFunction.Sum(wks.Range("F:F"))
        End If
    Next wks

End Function

This is not a volatile function, so if you add another sheet, or change a value in column F, the revised total will not show up until you force a recalculation by doing Ctrl + Alt + F9.

If you need a volatile function which will always the display the correct total, let me know. Volatile functions can make recalculation times slow, but that may not be an issue for you.

If you are adding the new sheets via VBA, there is a VBA instruction you can use to force the recalculation: Application.CalculateFull