incrementing column symbols orthogonally?
 

I have two formulas in two neighboring cells in the same column:
=A$1
=C$1

Is there a simple way or setting to copy this scheme to a cell block below
these two cells dragging and to increment the column symbol by two or more
letters? Here's an example:

I have:
=A$1
=C$1
------
I'd like to get below it:
=E$1
=G$1
=I$1
=K$1
....

z.entropic

Instead of =A$1 & =C$1, try this:
r1c1
r1c3 (without the "=" )
Now, select both of these, use the fill handle as far as necessary.
This will give
r1c1
r1c3
r1c5
r1c7
etc.
Now use Tools/Options/General and select R1C1 notation.
Then select these text values and use Edit/Replace and replace r with =r
Switch back to A1 notation (deselect R1C1), and you'll see
=$A$1
=$C$1
=$E$1
=$G$1
etc.

If you prefer, you can do it with formulas:
in row 1 somewhere, enter
=OFFSET($A$1,0,(ROW()*2-2))
and fill down.

HTH
Bob Umlas
Excel MVP


"z.entropic" wrote in message
...
I have two formulas in two neighboring cells in the same column:
=A$1
=C$1

Is there a simple way or setting to copy this scheme to a cell block below
these two cells dragging and to increment the column symbol by two or more
letters? Here's an example:

I have:
=A$1
=C$1
------
I'd like to get below it:
=E$1
=G$1
=I$1
=K$1
...

z.entropic

I can roundabout get you there.
Column A...
A1 = 1
A2 = A1+2
copy down

Column B...
B1 =ADDRESS(1,A1,4)
copy down

Column C...
C1 =INDIRECT(B1)
copy down

HTH,
Gary Brown



"z.entropic" wrote:

I have two formulas in two neighboring cells in the same column:
=A$1
=C$1

Is there a simple way or setting to copy this scheme to a cell block below
these two cells dragging and to increment the column symbol by two or more
letters? Here's an example:

I have:
=A$1
=C$1
------
I'd like to get below it:
=E$1
=G$1
=I$1
=K$1
...

z.entropic

Ah! OFFSET is what I need. Thanks a lot to you and Gary.

z.entropic

"Bob Umlas" wrote:

Instead of =A$1 & =C$1, try this:
r1c1
r1c3 (without the "=" )
Now, select both of these, use the fill handle as far as necessary.
This will give
r1c1
r1c3
r1c5
r1c7
etc.
Now use Tools/Options/General and select R1C1 notation.
Then select these text values and use Edit/Replace and replace r with =r
Switch back to A1 notation (deselect R1C1), and you'll see
=$A$1
=$C$1
=$E$1
=$G$1
etc.

If you prefer, you can do it with formulas:
in row 1 somewhere, enter
=OFFSET($A$1,0,(ROW()*2-2))
and fill down.

HTH
Bob Umlas
Excel MVP


"z.entropic" wrote in message
...
I have two formulas in two neighboring cells in the same column:
=A$1
=C$1

Is there a simple way or setting to copy this scheme to a cell block below
these two cells dragging and to increment the column symbol by two or more
letters? Here's an example:

I have:
=A$1
=C$1
------
I'd like to get below it:
=E$1
=G$1
=I$1
=K$1
...

z.entropic