how do you copy the same cell reference on different worksheets

G

Guest

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!!
 
G

Gord Dibben

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell Reference 1
Link to same cell in different sheets 4
Reference(s) to worksheets for building graphs 4
Referencing another workbook using a variable for the sheet? 20
Code to format cells on several worksheets 2
Automatic Sumning of Same Cell from Different Sheets 3
Using an offset formula for the reference in a relative reference 6
Separate raw data onto different worksheets 4

Top