# Thread: Copying Cells (Excel 2000 SR1-a)

1. ## Copying Cells (Excel 2000 SR1-a)

I have a workbook that has a worksheet for each day of a month "1-31". I need a summary worksheet for one particular cell from each worksheet. I have set up a columnar worksheet in a sheet named 'Monthly'. The sheet lists Jun 1 in A2 thru Jun 30 in A31, in B2 the formula is ='1'!V52, if I copy that cell to the next cell (B3) I get ='1'!v53 which is the normal way Excel copies cells. Is there a way to copy the cell where only the Worksheet reference increases and the cell stays the same? in other words the next cell (B3) would be '2'!v52 instead of '1'!v53? shihalud

2. ## Re: Copying Cells (Excel 2000 SR1-a)

1. I don't think there is an easy way to make Excel treat the worksheet reference as relative, but you can make the cell reference absolute by selecting it and pressing F4, or by putting \$ signs in, which has the same effect: \$V\$52 will stay \$V\$52 when you fill down.

2. You can use the INDIRECT function to simulate relative worksheet references. Insert an empty column before column B (so that the present column B becomes column C).
In cell B2, enter 1, and in cell B3, enter 2. Select B2 and B3, then use the fill grip (the black square in the lower right corner) to fill this down as far as needed (perhaps double clicking the fill grip will do this). You'll get a series of numbers 1, 2, 3, 4, ...
In cell C2, enter the formula<pre>=INDIRECT("'"&B2&"'!V52")</pre>

and use the fill grip to fill down as far as needed.
If you like, you can hide column B now.

3. ## Re: Copying Cells (Excel 2000 SR1-a)

Put the following formula in B2 and copy it down as far as needed for the month:

<pre>=INDIRECT("'"&ROW()-1&"'!\$V\$52")
</pre>

4. ## Re: Copying Cells (Excel 2000 SR1-a)

Thanks for the info - both worked fine. shihalud

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•