excel- formula

[Guest]

I'm after a formula that will summarise several worksheets into a summary
sheet, within a workbook, at a given cell location.Possibly one which will
either be written to one cell, and 'copied' by dragging to other rows, and
columns, rather than clicking on each woksheet, to get original cell location

 

[Gord Dibben]

Dennis

Use of the INDIRECT Function is handy for incrementing sheet names in
formulas.

e.g.

Insert a new sheet and name it Namesheet.

Type your sheet names(other than Summary) in a list from A1:A10 (assuming 10
sheets)

In your Summary sheet enter formula like

=INDIRECT(Namesheet!A1 & "!" & "$A$4")

Drag/copy this down the column to return contents of A4 from each sheet in the
list from A1:A10


Gord Dibben Excel MVP

 

[David McRitchie]

An example of using INDIRECT along these lines can be seen in
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm

If you don't care what the names of the sheets are but just want
a SUM of a specific range (or a cell) from the same location on
a group of sheets between two sheet tabs (inclusive) you could
use a 3D reference.