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