Referencing data in another sheet 1

[gadero]

I'm trying to use the auto fill feature of excel to do the following:

Say I have data in column A, rows 1-25 on sheet 1. I want to from sheet 2 pull data from column A row 1, 3, 5, 7, etc. I tried using:
=sheet1!A1
=sheet1!A3

Then use the autofill feature, but get the following:
=sheet1!A1
=sheet1!A3
=sheet1!A3
=sheet1!A5
=sheet1!A5
=sheet1!A7

This is not what I want. I'm trying to get to:
=sheet1!A1
=sheet1!A3
=sheet1!A5
=sheet1!A7
=sheet1!A9
=sheet1!A11


Not sure what I'm doing wrong. Am I misapplying the autofill feature? Any help would be appreciated.


Thanks

 

[bltseattle]

Put this formula into cell a1 on sheet 2 and fill down

=OFFSET(Sheet1!$A$1,ROW(A1)*2-2,0,1,1)

 

[gadero]

Thanks seattle. That was so easy. Never used the offset command. Just played with the index command too (I think I can use that as well).

Appreciate the help.

 

[yogia]

Hi gadero:

Or you may use the following formula using the nonvolatile INDEX function in cell A1 of Sheet2 ...

=INDEX(Sheet1!A:A,(ROW()>1)*(ROW()-1)*2+1)

and copy it down.



Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com