Drag a formula right one cell, reference cell jumps right 2 cells?

Hi Mukul,

In the attached sheet I have put data as you have suggested . In sheet1 the below formula is used in cell A5 and autofilled formula to cells at right
=OFFSET(A1,0,COLUMN(A1)-COLUMN($A$1),1,1)
It fetches data from first row as I have assumed that the values start from cell A1.


In the sheet2 the formula is used in cell A6 and auto filled to cells at right and down.
=OFFSET(A2,0,COLUMN(A2)-COLUMN($A$2),1,1)

Note :You can change the reference of data but the absolute col and row reference eg.COLUMN($A$2) above should be same as the first column of data set.

Thank You Bhuvanesh.. .

I can never thank you enough for this. )

Originally Posted by Bhuvanesh

Hi Mukul,

In the attached sheet I have put data as you have suggested . In sheet1 the below formula is used in cell A5 and autofilled formula to cells at right
=OFFSET(A1,0,COLUMN(A1)-COLUMN($A$1),1,1)
It fetches data from first row as I have assumed that the values start from cell A1.


In the sheet2 the formula is used in cell A6 and auto filled to cells at right and down.
=OFFSET(A2,0,COLUMN(A2)-COLUMN($A$2),1,1)

Note :You can change the reference of data but the absolute col and row reference eg.COLUMN($A$2) above should be same as the first column of data set.