how do you copy the same cell reference on different worksheets

  • Thread starter Thread starter Guest
  • Start date Start date
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!!
 
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
 
You must log in or register to reply here.

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
Referencing another workbook using a variable for the sheet? 20
Link to same cell in different sheets 4
Code to format cells on several worksheets 2
Reference(s) to worksheets for building graphs 4
Define Name - same name for multiple cells on multiple sheets 6
Sheet Numbers Q 11
How to create a 'name' which is not worksheet spcific 1

Back
Top