Formula - Across sheet calculation with single reference...

Suppose you have some data on three sheets at same addresses...
You wanna make apply certain formula in first sheet for the whole data...

e.g.
You have three sheets named Sheet1, Sheet2, Sheet3..
Data is available in location A1:A10 at all sheets..

Now u wanna Sum the whole data spread over all sheets in First sheet...

There are two metnohds to do this...

• Simple and Long method:
   
  =SUM(A1:A10)+Sum(Sheet2!A1:A10)+Sum(Sheet3!A1:A10)
   
• Simple and Short method:
   
  =SUM(A1:A10,Sheet2!A1:A10,Sheet3!A1:A10)

There exists 3rd method also...

• Smart n Short method:
   
  =SUM(Sheet1:Sheet3!A1:A10)

Yippe and the decoding of the formula is hereunder...

• "Sheet1:Sheet3!" means all sheets from and coming in between in series Sheet1 and Sheet3
   
• "A1:A10" means Cell A1 to A10 for all sheets coming within the range as above.

Improtant to note, if we remove "Sheet2" from in between "Sheet1" and "Sheet3" and place in before "Sheet1" or after "Sheet3" then data of "Sheet2" will not be included in "Sum"

This is called grouping of sheets and any formula can be applied through this grouping.

Lemme elaborate how to apply formula:

1. type "=Sum("
2. Hold down "Shift" key
3. Click on destination sheet backward or forward upto which range is required, like "Sheet3" in above example. Notice that formula reference has been changed.
4. Now in the destination sheet, select the range u like to take into reference, like "A1:A10"

Vooho, it's done...