# 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

The contents of B4 should be =A4, and B5 should be =A25.

• Re: Copy Formula With Fill Handle

You are right Cringe. It is still undesirable

edit: Ding. post 100:cool:

• Re: Copy Formula With Fill Handle

The pattern has to be repeatable. Do B3 and B4 and drag that.

• 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

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

perfect!
and thank you others for your time.

• 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)

• Re: Copy Formula With Fill Handle