Changing References Down A Column

  • Contents of B2 are "=A2"
    Contents of B3 are "=A23"


    I want to select B2 and B3 and drag the formula to continue


    Desired results:
    Contents of B4 are "=A44"
    Contents of B5 are "=A65", but when I drag the fill handle...


    Actual results:
    Contents of B4 are "=A4"
    Contents of B5 are "=A25"


    Thanks


    edit: corrected numbers for accuracy

  • Re: Copy Formula With Fill Handle


    I do not understand what you mean by

    Quote from Kenneth Hobson

    Do B3 and B4 and drag that.


    B4 is currently empty.



    I see a pattern.
    =cell
    =cell+21 rows
    =cell+21 rows+21 rows
    =cell+21 rows+21 rows+21 rows


    or
    =cell
    =add 21 rows to above cell reference
    =add 21 rows to above cell reference


    Does excel just not do this?

  • Re: Copy Formula With Fill Handle


    Kenneth is advising you to fill in cell B4 and B5 with the desired result:


    B4 "=A4"
    B5 "=A25"


    and then autofill. Excel needs to see a trend to mimick one.

  • Re: Copy Formula With Fill Handle


    B4 "=A4"
    B5 "=A25"
    are not the desired results.


    I have put my desired trend for numerous rows and excel doesn't recognize what I'm trying to accomplish.
    I have the following selected for example


    B2 "=A2"
    B3 "=A23"
    B4 "=A44"
    B5 "=A65"
    B6 "=A86"


    I wonder if I am not explaining something correctly/clearly.

  • Re: Copy Formula With Fill Handle


    In order for the pattern to be established, you have to select more than two cells. i.e. select B2:B5; then Excel will understand the pattern and fill properly.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

  • Re: Copy Formula With Fill Handle


    Quote from shades

    In order for the pattern to be established, you have to select more than two cells. i.e. select B2:B5; then Excel will understand the pattern and fill properly.


    /face palm


    I have done this, as mentioned in post 7

  • Re: Copy Formula With Fill Handle


    This may not be the cleverest way, but you can do what you what using INDIRECT:


    Assuming the pattern of references in successive rows is as described, use something like


    =INDIRECT("a"&((ROW()-2)*21)+2)


    in cell B2, and then copy this down

  • Re: Copy Formula With Fill Handle


    Glad you have what you want.


    Quote from joseph_r03

    /face palm


    I have done this, as mentioned in post 7


    Actually, when I did it and extended, it worked fine. Guess I'm not catching what you wanted. But as old as I am that is not surprising.


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!