Copying Formula Patterns

Gavin Harrison

New Member
Joined
May 2, 2017
Messages
26
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,546
Office Version
365
Platform
Windows
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)
 
Last edited:

chleb

New Member
Joined
Apr 25, 2017
Messages
8
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
 

Forum statistics

Threads
1,084,796
Messages
5,379,933
Members
401,633
Latest member
DinnerB0ne

Some videos you may like

This Week's Hot Topics

Top