CODEWITHSUNDEEP
excelexcel-formularowcopy-paste
Falk
Falk

Reputation: 86

Easy way to copy formula extending the source row by 1?

Is there an easy way to copy a formula (see "What I want", in that case simply =B4 =B5 =B6) increasing the source row by 1 instead of the excel standard taking the row in which it is copied (see "What I get", in that case =B4 =B6 =B8)? The pattern of the distance where the formula is copied is always the same (in that example always jumping over one cell). There is no offset in the source cells/rows.

enter image description here

Upvotes: 1

Views: 613

Answers (2)

pnuts
pnuts

Reputation: 59475

Slightly shorter:

=IF(ISODD(ROW()),"",OFFSET(B$4,(ROW()-4)/2,))

OFFSET

IF(ISODD(ROW()),"",....) just returns blank every odd numbered row. (In some cases ISODD should be changed to ISEVEN.)

B$4 is the starting point, and is anchored ($) to remain the reference as the formula is copied down.

ROW returns the current row number and we deduct 4 from it when starting the formula in Row4 to copy a cell in that row (so when placed in Row4 Row()-4 gives 0 offset).

As the formula is copied down ROW() increments by 1 each row and since we only want it to increment by 1 every second row, /2.

Hopefully it is fairly obvious what to change if to start in a different row or to interleave with more than a single blank row.

Move the anchor ($B4 rather than B$4), use COLUMN instead or ROW and move the final , in OFFSET forward one position (ie next to the other) if to copy across rather than down.

Upvotes: 1

Mrig
Mrig

Reputation: 11702

Assuming data starts from Cell B4 then in Cell C4 enter the following formula

=IF(MOD((ROW()-4)*0.5,1)=0,OFFSET($B$4,(ROW()-4)*0.5,0),"")

Drag/Copy down above formula as required. See image from reference.

enter image description here

Upvotes: 3

Related Questions