how do you copy the same cell reference on different worksheets
 

Hi,
On one of my workbooks (Excel Windows XP Pro), I have information on
separate worksheets (sheet 1, sheet 2, sheet 3, etc). On my last sheet, I
would like to reference the same cell (ex. A1) on each of the previous
sheets. Rather than clicking on each sheet to reference (ie. =sheet1!a1,
=sheet2,!a1), is there a way to click and drag so the cell stays the same
(A1) but the sheets change?

Let me know.
Thanks!!

abee

If Sheet1, Sheet2 etc. enter this formula in A1 of your master sheet.

=INDIRECT("'Sheet" & (ROW() & "'!A1")

Drag/copy down column B.

If sheets have unique names, enter the sheet names in a column and use this
formula.

=INDIRECT(B1 & "!A1")

If spaces in sheetnames use =INDIRECT("'" & B1 & "'!A1")

Assume sheet names were in B1:B10 you would copy down 10 cells.

Your ranges may differ, so adjust to suit.


Gord Dibben Excel MVP

On Tue, 14 Jun 2005 17:25:01 -0700, "abee"
wrote:

Hi,
On one of my workbooks (Excel Windows XP Pro), I have information on
separate worksheets (sheet 1, sheet 2, sheet 3, etc). On my last sheet, I
would like to reference the same cell (ex. A1) on each of the previous
sheets. Rather than clicking on each sheet to reference (ie. =sheet1!a1,
=sheet2,!a1), is there a way to click and drag so the cell stays the same
(A1) but the sheets change?

Let me know.
Thanks!!