Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 Last edited by Chris Mizon : March 8th 12 at 11:52 AM |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Losing rows that reference data from another worsheet | Excel Worksheet Functions | |||
Incrementing cell reference | Excel Discussion (Misc queries) | |||
Excel: hold a reference to a single cell when copying formulas? | Excel Worksheet Functions | |||
Excel: hold a reference to a single cell when copying formulas? | Excel Worksheet Functions | |||
incrementing formula reference by 7 | Excel Discussion (Misc queries) |