Referencing alternate rows (Harder than it sounds)

[whohasmysock]

Say I have two columns, A and B. In column B are all the numbers from 1 to 100 (In actuality it will be text, but for simplicity's sake lets say its the numbers 1 to 100).
In column A I want to write a formula to reference all of the odd numbers in order, starting with the first, so that A1=1, A2=3, A3=5, and so on. I need a formula that can be entered once (into cell A1) and easily dragged / auto filled to the end of the list, for the rest of the entries.
Basically you know how if you type "1", then "2", then drag the box excel fills in the rest as "3,4,5,6.....etc"? Or how if you start in cell A1 with "=C1" and then when you drag that cell down, A2 will be "=C2", A3 will be "=C3" and so on. What I am trying to accomplish is to have A1 "=C1", A2 "=C3", A3 "=C5" and so on, WITHOUT having to manually enter in each cell. Are there any functions or shortcuts, or even any WAY to do this?

 

[jbeaucaire]

Try this for odd rows starting in A1:

=INDIRECT("C" & ROW() * 2 - 1)
...and copy down.

For even rows:
=INDIRECT("C" & ROW() * 2)

 

[whohasmysock]

you, sir, are a genius.
Now, is there a way to make that work between separate sheets? if the 1-100 numbers was on sheet1, and I wanted the odd numbers in column A on sheet2, and the even numbers in column B on sheet2?

 

[jbeaucaire]

The INDIRECT() function is dealing with strings, so you can put whatever strings in there you want, including strings that are actually other sheets:

=INDIRECT("Sheet1!C" & ROW() * 2 - 1)
...and copy down.

For even rows:
=INDIRECT("Sheet1!C" & ROW() * 2)