How to drag a cell reference formula down a column, but move the cell reference across a row

Question

I am trying to drag a cell reference formula down a column, but have the row value increase as the formula is dragged down.

For instance, in the shot below, cell B3 has the formula "=I3". I would like to be able to drag the formula down column B so that B4's value would be "=J3", the value in B5 would be "=K9" etc..

Link to page with sample spreadsheet

Thanks,

Chris

patrick2788 · Answer

curryguy&nbsp;To make it dynamic, you might use:&nbsp;=LET(rng,I3:ZZ3,nonblanks,COUNT(rng),dynamic,TAKE(rng,,nonblanks),TRANSPOSE(dynamic))&nbsp;&nbsp;If it's a one-off, you could simply try:&nbsp;=TRANSPOSE(I3:K3)&nbsp;Fill handle not needed.&nbsp;

Forum Discussion

How to drag a cell reference formula down a column, but move the cell reference across a row

Share

Resources