Copy Down Formula B2,B12,B22...

[ClowWater]

I know this has to be a common question but I could not find a posting on it through a google search.
This is what I have in ColA
1
4
5
7
3
9
6
3
4
5
In colB I want to put the following formulas
=a2
=a12
=a22

I have about 1000 rows so I didn't want to type it out and thought Excel would be smart enough to copy down the correct pattern when I highlighted B1-B3 but it came up with some wierd pattern.

There has to be a simple way to get Excel to copy this down? Or do I have to write a more complex formula?

 

[Expiry]

One way aroudn this is to type the following:

B1 =INDIRECT("A"&C2)
C1 2
B2 =INDIRECT("A"&C2)
C2 12

Select all four cells and drag down as far as you need using the handle.

 

[ClowWater]

This will work...but one more wrinkle.
Say I want ColD
=A3
=A13

I was thinking something like: =INDIRECT("A"&C2-1) but that didn't work...I need to get the value of C2 and then subtract 1. Any ideas?

 

[Expiry]

=INDIRECT("A"&C2+1) should work. If C2 contains 12, then you can use that to give you A13.

In my first post, B1 should contain =INDIRECT("A"&C1), I'm sure you worked that out.