Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Arlington, Texas, USA
    Posts
    124
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •