Copying Formula Patterns

[Gavin Harrison]

Hi.

I'm wanting to copy a formula down a column following a pattern, but obviously just by dragging the formula down the cells underneath doesn't work.

Ive seen a few examples but I'm struggling to understand them.

Essentially I want the sequence to be as follows:

='Sheet1'!A1
='Sheet1'!A3
='Sheet1'!A5

Any help greatly appreciated.

Thanks
Gavin

 

[Peter_SSs]

Exact detail depends on what cell your first formula is in. For example, if the first formula is in cell D2 of the other sheet, try this, copied down.

=INDEX(Sheet1!A:A,ROWS(D$2:D2)*2-1)

Edit:
If it is possible that new rows subsequently inserted at the top of Sheet1 might cause the formula to return incorrectly results, then try something like this instead.

=INDEX(Sheet1!A$1:A$100,ROWS(D$2:D2)*2-1)

 

[chleb]

Hi Gavin,

I'm not sure if this is the solution, but adding dollar sign '$' before 'A' makes it not changing while dragging the formula. The same can be applied to '1' so for example: when "='Sheet1'!$A1" is dragged 'A' always stays, but '1' is changing. "='Sheet1'!$A$1" when dragged is always the same.

I hope it helps.

Have a nice day,
chleb

 

[Gavin Harrison]

Thanks Peter, that worked like a charm!!

 

[Peter_SSs]

Thanks Peter, that worked like a charm!!

You are welcome. Thanks for letting us know.