Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Nicole
 
Posts: n/a
Default Excel 2003 - Copy Cell Reference

I have a cell reference in my last worksheet of my workbook e.g. =Sheet1!B5

All the other worksheets are set up in the same way and so I am trying to
copy this refence so that I would get =Sheet2!B5 =Sheet3!B5 etc. so that it
saves me time not having to change all the cells manually. My guess is, this
is not possible as worksheets are absolute and not relative?! Or is there any
smart way how to get around this problem?

Kind regards

Nicole

  #2   Report Post  
Gord Dibben
 
Posts: n/a
Default

Nicole

One method.....

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

=INDIRECT("'Sheet" & (ROW() & "'!$B$5")

Drag/copy down column A.

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

=INDIRECT(X1 & "!$B$5")

If spaces in sheetnames use =INDIRECT("'" & X1 & "'!$B$5")

Assume sheet names were in X1:X10 you would copy down 10 cells.

Your ranges may differ, so adjust to suit.


Gord Dibben Excel MVP




On Tue, 6 Sep 2005 17:09:20 -0700, Nicole
wrote:

I have a cell reference in my last worksheet of my workbook e.g. =Sheet1!B5

All the other worksheets are set up in the same way and so I am trying to
copy this refence so that I would get =Sheet2!B5 =Sheet3!B5 etc. so that it
saves me time not having to change all the cells manually. My guess is, this
is not possible as worksheets are absolute and not relative?! Or is there any
smart way how to get around this problem?

Kind regards

Nicole


  #3   Report Post  
Nicole
 
Posts: n/a
Default

Cool!!! You are the best! :)

Many thanks!

Nicole

"Gord Dibben" wrote:

Nicole

One method.....

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

=INDIRECT("'Sheet" & (ROW() & "'!$B$5")

Drag/copy down column A.

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

=INDIRECT(X1 & "!$B$5")

If spaces in sheetnames use =INDIRECT("'" & X1 & "'!$B$5")

Assume sheet names were in X1:X10 you would copy down 10 cells.

Your ranges may differ, so adjust to suit.


Gord Dibben Excel MVP




On Tue, 6 Sep 2005 17:09:20 -0700, Nicole
wrote:

I have a cell reference in my last worksheet of my workbook e.g. =Sheet1!B5

All the other worksheets are set up in the same way and so I am trying to
copy this refence so that I would get =Sheet2!B5 =Sheet3!B5 etc. so that it
saves me time not having to change all the cells manually. My guess is, this
is not possible as worksheets are absolute and not relative?! Or is there any
smart way how to get around this problem?

Kind regards

Nicole



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
how do you copy the same cell reference on different worksheets abee Excel Worksheet Functions 1 June 15th 05 01:48 AM
How do I get top cell alignment in excel 2003 marty53 Excel Discussion (Misc queries) 4 April 2nd 05 05:05 PM
Enter an Excel cell reference as part of a custom header/footer Suegi123 Excel Worksheet Functions 1 April 1st 05 10:55 PM
Can't Copy and Paste between Excel 2003 Workbooks wllee Excel Discussion (Misc queries) 6 March 30th 05 02:59 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 12:36 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"