Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Strange Behavior When Copying Down a Column (EXCEL97/SR2)

    I often want to copy a cell, that I have entered an equation into, down the length of column(s) with data referenced in the equation. So, I end down the column with the data and insert any character in the corresponding row of the equation column. Then, I arrow-end-up to the cell with the equation and copy it down to the "stop" value I inserted. It sounds complicated but I have always done it this way.
    Now the strange behavior: Lets say, for example, that the equation was entered into cell D2 and the "stop" value is entered into D500. I go through my procedure and check and sometimes the cells referenced by my equation (below row 2) are all off by about 500. Specifically, the equation I copied into D3 is referencing data in row 501 rather than row 3 and the equation in cell D500 is referencing data in row 998.
    I am trying to convert to the easier method of double clicking the right handle of the equation cell but at times I forget. Anyway, if you know why this happens and think it is a non-trivial subject, I would be interested in hearing from you. <img src=/S/blank.gif border=0 alt=blank width=15 height=15>

  2. #2
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Behavior When Copying Down a Column (EXCEL97/SR2)

    Your description is hard to follow, the second half a little easier to understand. If the equation you entered into cell D2 uses relative references to cell C2 (or whatever, nearby), then when you copy it down to the other cells (D3500), the relative references in each of the cells should still refer to cells in their own immediate vicinity. You do not tell exactly what method you use to copy; do you drag the fill handle, or do you Ctrl-C the cell with the formula and paste it to a range, or what? If you can give a blow-by-blow of exactly what you do that causes the bhavior (instead of a generic example), it would be more helpful.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Strange Behavior When Copying Down a Column (EXCEL97/SR2)

    I'd say what is heppening here is that when you End down, excel still thinks you are entereing the formula, and inserts the cell you end down to in say D2. If you are entering a formula in D2, and before you actually press enter, you go to C2 and then end down, you are entering whatever the selected cell is into the formula.

    What might be better to do is enter the formula in D2, copy it, hop over to C2, end down, then go to D500, say, and holding Shift, end up, and paste.

    Andrew

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Strange Behavior When Copying Down a Column (EXCEL97/SR2)

    Hey, thanks for the quick replies. I may not have explained this well enough but I can't deal with it now because I got a real bad problem that I really need help with. Please see my next post. This is a forecasting application that relies on a pivot table to bring data together for the forecast. The PT and everything has been fine but now something has gone out of wack and it is giving me a message that I have too many rows or columns. But it had worked fine before for months and all I did today was to some new equations to a column. Anyway, I hope you can read about it and give me a clue.

Posting Permissions

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