Multiple worksheets in workbook

  • Thread starter Thread starter Chad Somerset
  • Start date Start date
C

Chad Somerset

I have a workbook with more than 100 worksheets and I want
to create a summary page of specific cells in each sheet.
How can I quickly create a summary page, where a row has
the worksheet name and links to specific cells in each
sheet?
 
Chad

The INDIRECT function may work for you.

=INDIRECT(A1&"!&$B$1) entered in C1

A1: Sheetname
B1: A1

formula above generates =Sheetname!A1

Your sheetnames will be listed in column A and reference cells in column B

this formula is better allows for spaces in sheetname

=INDIRECT("'"&A1&"'!"&$B$1)

If your sheetnames are the generic Sheet1, Sheet2 etc you could use

=INDIRECT("Sheet" & ROW() & "!" & $B$1)

Making use of Absolute and Relative references gives you a range of returns.

Gord Dibben Excel MVP XL2002
 
Back
Top