Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
abee
 
Posts: n/a
Default 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!!
  #2   Report Post  
Gord Dibben
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
reference cell value from fixed column with variable row bob z Excel Discussion (Misc queries) 0 May 23rd 05 11:30 PM
Copy text from same cell on every sheet to title sheet? Jon Excel Discussion (Misc queries) 2 February 9th 05 04:11 PM
HOW DO I COPY FORMULA WITHOUT CHANGING CELL REFERENCE anantth Excel Discussion (Misc queries) 4 February 6th 05 01:25 PM
sending data from one cell to another (not using copy & paste) Charlie Payne Excel Worksheet Functions 3 December 6th 04 04:28 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 03:52 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"