help copying a formula.

[tu0nie]

I need to copy a formula down and have it increase the column letter. For example

=A$1 and copy that down so it goes to
=B$1
=C$1 and so on and only increase the letter and not the number. I know a person can do this horizontaly but I need this copy procedure to work vertically.

This looks so easy but I can not find the answer anywhere. Any help would be greatly appreciated.

Thanks.

[Leith Ross]

Hello tu0nie,

A relative reference will only change when you drag it in the direction of the relative reference. A relative row will only change if you drag the formula vertically, and a relative column will only change when you drag the formula horizontally. This holds true whether your workbook is setup for A1 style or R1C1 style referencing.

Sincerely,
Leith Ross

[daddylonglegs]

In which cell do you want the formula?

If you want A1's value in D3, B1 in D4 etc. try this formula in D3 copied down

=OFFSET(A$1,,ROW()-ROW(D$3))

[tu0nie]

B1 through ZZ1 has
"To"

B2 shows
"1" which it gets from a formula

C2 shows
"2" which it gets from a formula

and so on increasing by 1

CELL J11 has
=IF(OR($B11="",$C11=""),"",IF((ISNA(VLOOKUP($B11,C$11:P$260,6,FALSE))),$H11,(MAX((DMAX($B$10:$P$260,$O$10,B$1:B$2)),$H11))))

and when I copy this down I want cell J12 to have
=IF(OR($B11="",$C11=""),"",IF((ISNA(VLOOKUP($B11,C$11:P$260,6,FALSE))),$H11,(MAX((DMAX($B$10:$P$260,$O$10,C$1:C$2)),$H11))))

J13
=IF(OR($B11="",$C11=""),"",IF((ISNA(VLOOKUP($B11,C$11:P$260,6,FALSE))),$H11,(MAX((DMAX($B$10:$P$260,$O$10,D$1:D$2)),$H11))))

and so on.

I can enter all these in by hand but I need to go up into the 1000's and this would take me a long time so I was hoping there was some trick to copy this. If there is I could use it in the future as well. Hopefully this clears up my problem some.

Thanks.

[daddylonglegs]

You can still use OFFSET as I suggested above. Try replacing B$1:B$2 in the J11 formula with

OFFSET(B$1:B$2,,ROW()-ROW(J$11))

[tu0nie]

Thanks a bunch.

I thought I switched it out but I must of had something wrong. Works perfect now.

Thanks again for your time.