Copying formulas but incrementing the worsheet reference

[Chris Mizon]

Hi all,

Excel amatuer hear so please be extra nice!

I have the following fomula in cell B5:

='Staff Member 1'!B$7

Staff Member 1 is a reference to a worksheet later in the workbook an
there are others up to Staff Member 62. This workbook is used to recor
staff training and then there are overview pages for training complete
and due hence the need for the formula above.

There are many columns with many rows so I do not wish to manually typ
each formula manually as that will take days. Therefore, I would like t
copy the above formula down column B to row 66 in the following way:

Cell B5 ='Staff Member 1'!B$7
Cell B6 ='Staff Member 2'!B$7
Cell B7 ='Staff Member 3'!B$7
.....
....
Cell B66 ='Staff Member 62'!B$7

However, using the standard handle grab, pull down copy method result
in:

Cell B5 ='Staff Member 1'!B$7
Cell B6 ='Staff Member 1'!B$7
Cell B7 ='Staff Member 1'!B$7
.....
....
Cell B66 ='Staff Member 1'!B$7

Is there a way in which I can copy the formula but increment the shee
reference?

Many thanks in advance for your assistance.

Chr

 

[Claus Busch]

Hi Chris,

Am Thu, 8 Mar 2012 09:36:48 +0000 schrieb Chris Mizon:

Cell B5 ='Staff Member 1'!B$7
Cell B6 ='Staff Member 2'!B$7
Cell B7 ='Staff Member 3'!B$7
....
...
Cell B66 ='Staff Member 62'!B$7

in B5 try:
=INDIRECT("'Staff Member "&ROW(B1)&"'!B$7")


Regards
Claus Busch