Formula adjust down column as it autofills right

[XLN00B]

Hi Guys, 

I am making a cashflow forecast, and on one sheet "Historic Data", I have created a table with both manual and formulaic analysis - on this sheet the data naturally flows down the page, where the columns represent each category of data  i.e. date, commission, sales etc..

On the cash flow forecast page, the timeline moves from left to right. 

I have created a basic formula which is designed to take data as per availability from the 'Historic Data' worksheet:

=IF('Historic Data'!J65=0,'Historic Data'!I65,IF('Historic Data'!I65=0,'Historic Data'!U65,'Historic Data'!J65))

When I drag this formula to the right, excel will auto completed and adjust the cells to the right also eg:

=IF('Historic Data'!K65=0,'Historic Data'!J65,IF('Historic Data'!J65=0,'Historic Data'!V65,'Historic Data'!K65))

Is there a way I can make it so that the formula autofill's, but moves down the column, so that when I drag to the right I get the following outcome: =IF('Historic Data'!J66=0,'Historic Data'!I66,IF('Historic Data'!I66=0,'Historic Data'!U66,'Historic Data'!J66))

Any help would be greatly welcomed and appreciated! 

[#2 · Accepted solution]

@XLN00B 

Try In cell C65, input 
=IF(
      OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,0)=0,
      OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,-1),
      IF(
          OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,-1)=0,
          OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,11),
          OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,0)

     )

  )

Instead of using J65, I65, U65... , 
Use Formular OFFSET('Historic Data'!$J$65,COLUMN(C64)-3,0)

- Manual change  0 when you want to refer to different column, e.g. 1 for K65, 2 for L65

- Drag right will auto-increase COLUMN() parameter eg. from  COLUMN(C64)-3 to COLUMN(D64)-3, which means from offset 0 row to 1 row, i.e. J65 to K65

[#3]

Thank you @XXplore - I literally copy and pasted your formula, and this worked perfectly!

Now I must try and understand how this works at a detailed level, and implement in other aspects of this sheet.

Really appreciate your guidance, and the effort that you have taken to respond to me so thoroughly :)