Excel Video 251 covers one more worksheet tab trick I think you’ll find helpful. We’ve discussed how to write formulas to capture data from multiple tabs. While that’s very helpful, if you have 12 tabs for each month of the year, selecting all 12 cells on 12 monthly tabs will take you a while and the chance of selecting the wrong cell is pretty high. Here’s a shortcut to make selecting the same cell or group of cells from multiple worksheets easier.
The formula is =SUM(Dashboard:Test!A4), where Dashboard is the name of the first worksheet and Test is the name of the last worksheet to sum. If I change the formula to =SUM(Dashboard:Test!A4:C4), I’m summing cells A4 through C4 on the tabs instead. You aren’t just limited to the SUM function either. The next time you have multiple similar worksheets, see if these formulas don’t save you a bunch of time.