Forum Discussion
Need 1 worksheet to add totals from multiple worksheets in the same file.
Try the following formula-
=SUM('*'!A1:A100)
I’ve seen proposals for using '*'!A1:A100 to sum across sheets—great creativity!
While wildcards are convenient here, they are risky. It is better, and above all safer, to use =SUM(Sheet1:Sheet3!A1) or explicit references to ensure reliability!
Excel deliberately restricts worksheet references in formulas to explicit names or contiguous ranges to ensure clarity, security, and performance. The wildcard character * is not supported, as it would lead to ambiguities and risks that contradict Excel's design principles. Microsoft doesn’t explicitly state, "We restrict wildcards in sheet references to avoid ambiguity," but their documentations implies it throughFor dynamic or non-contiguous sums, VBA or structured, defined names are, in my opinion, the most reliable solutions.
Wildcards (* and ?) in Excel are safe and powerful when used in specific functions designed for pattern matching.
Like Check if a cell contains a substring.
Example in SEARCH/FIND for partial text matches
=IF(ISNUMBER(SEARCH("*apple*", A2)), "Found", "Not Found") // Case-insensitive
=IF(ISNUMBER(FIND("*apple*", A2)), "Found", "Not Found") // Case-sensitive
Nevertheless, it's a nice approach, thank you for sharing🙂.
NikolinoDE although I don't disagree use of a wildcard for sheet reference seems risky, but to your point and as noted by myself, Lorenzo and Harun24HR, Excel REPLACES that wildcard with actual references so the final formula IS an explicit reference. So it is just a short cut to easily enter the formula.
That said, I agree that using any sheet range should be done with care. For example if you have Sheet1 through Sheet5 in order and you type: =SUM('Sheet1:Sheet5'!A1) then the following scenarios may occur:
- you move Sheet3 to be after Sheet5, then Sheet3 is no longer in the sum range.
- you move Sheet5 to immediately after Sheet1 then Shee2:Sheet4 are no longer in sum range
- you move Sheet5 before Sheet1 then the range changes to Sheet1:Sheet4
- and if you try to 'undo' the move you just did by moving it back where it was, it will not necessarily 'undo' but rather treat the move as a new move so scenarios 1&2 above are effectively 'undone' but scenario 3 is not.
So a must 'safer' reference formula would be to list every sheet out: =SUM(Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1,Sheet5!A1)
BUT.... even though sheet ranges have risks they also have advantages. So if you have a Tab for each employee and grouped by active and then inactive and SUM across all active employees, that make it very easy formula to write and see: =SUM('EmployeeA:EmplyeeZ'!A1)
And it is super easy to move an Employee from Active to Inactive by moving their tab out of the Active range to the Inactive range. BUT if you move your first or last active employee you will mess up that SUM. For example, if you move your last active employee to the end of the inactive list you just expanded the range to include all of those inactive employees. That is why when ever I have a workbook where I use this technique (note: I will usually 1st consider and/or recommend changing the whole workbook to have a master data input with emplyee as a column and then one or more reports/outputs that will show the information for an individual employee, but they won't or cannot do that ...) I will recommend or insist on adding 'helper' tabs like 'Start' and 'End' to define the Tab range to prevent or at least minimize the liklihood of moving tabs causing an issue.So I know I'm probably 'preaching to the choir' for the most part, but for the OP and others that may read this thread I thought it would be of value. And back to the interesting use of a wildcard in facilitating formula entry, I go back to my "I don't know how I would use this" statement as I am very careful how I would use any Sheet range and don't think this trick, although very cool, will have many, if any, practical use in how I do things.
