Copying formulas but incrementing the worskheet reference
 

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 and there are others up to Staff Member 62. This workbook is used to record staff training and then there are overview pages for training completed and due hence the need for the formula above.

There are many columns with many rows so I do not wish to manually type each formula manually as that will take days. Therefore, I would like to 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 results 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 sheet reference?

Many thanks in advance for your assistance.

Chrs

Copying formulas but incrementing the worsheet reference
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2