How can I retrieve data from the same cell, from multiple sheets?

A

Alex Costache

Hello,

I have a Worksheet with many sheets (~20-30) and I want to use the data from
these cells and to summarize it in a single sheet. At the moment I use the
regular formulas (SUM(A1,A2,A3) but I end up with very long formulas, hard to
handle.

Is there a formula that could SUM/AVERAGE/COUNT the data from the same cell
(i.e. H12), from all sheets of the document? This would be very useful
because when I add a sheet I would not have to change all formulas again.

Thank you.
 
T

Toppers

One way is to add to "dummy" sheets named (for example) "Start" & "End".
Place the sheets to be summed etc between these two tabs i.e
Start,Sheet1,Sheet2 ....,End.


In your summary sheet, which is (obviously) not in the Start/End block, use:

=Sum(Start:End!A1:A5)

HTH
 
Top