Copying a formula to non-adjacent cells 1

[Annelies]

Hi

I have a formula that I want to copy to non-adjacent cells, however I want the references in that formula to stay concurrent.

For example, I want:

A1: =Sheet1!A1
A3: =Sheet1!A2
A5: =Sheet1!A3

But if I do a standard copy and paste of the formula I get

A1: =Sheet1!A1
A3: =Sheet1!A3
A5: =Sheet1!A5

I am going to need to copy this down quite a few rows, so would rather not reset every cell reference manually.

Can anyone help?

Thanks

Annelies

 

[byundt]

Annelies,
You might try an INDEX formula like:
=INDEX(Sheet1!A:A,INT((ROW()+1)/2))

Note that you'll need to adjust the formula if it isn't actually taking data from Sheet1!A1 and putting it into A1 on the other worksheet. For example, if you want to take data from A3:Axx and put into a range starting in B4:
=INDEX(Sheet1!A$3:A$1000,INT((ROW()-2)/2))
Brad