Summarising data in multiple sheets

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

Hi. I regularly create reports of 20 to 30 sheets and need to create a
dynamic link to the same cell in each sheet, the result being displayed
on a summary sheet in either rows or columns e.g. link to cell B3 in
selected Sheets (say 20 sheets) displayed in named sheet, in a column
from a named cell downwards, say C5. This would list dynamically 20
values in a seperate sheet from C5 to C24. How can this be done. Is
there an add-in? Thanks for any help. Nick

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software! Free Support at
http://www.ozgrid.com/forum/ **
 
... e.g. link to cell B3 in selected Sheets (say 20 sheets)
displayed in named sheet, in a column from a named
cell downwards, say C5. This would list dynamically 20
values in a seperate sheet from C5 to C24.
Click to expand...

Assuming the selected sheets are sequentially named:
Sheet1, Sheet2, Sheet3, ... Sheet20

Try in C5: =INDIRECT("'Sheet"&ROW(A1)&"'!B3")
Copy C5 down to C24

The formula in C5 is functionally equivalent to
putting in C5: =Sheet1!B3

As you copy down, the ROW(A1) part will
increment nicely to return correspondingly:

in C6: =Sheet2!B3
in C7: =Sheet3!B3
and so on

To increment in the same way for copying *across*,
use COLUMN(A1) in place of ROW(A1),
i.e. put in C5: =INDIRECT("'Sheet"&COLUMN(A1)&"'!B3")
Copy C5 across 20 columns
 
Thanks Max. The problem is that the sheets are individually named, a
opposed to sequentially. I guess I could change the name to sequentia
numbering on a temp basis then change back after. Only other way
guess would be BBA code, but that's an area uncetain to me. Nic
 
Back
Top