Forum Discussion

Copper Contributor

Feb 13, 2023

Solved

Formula adjust down column as it autofills right

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,...

Formulas and Functions

XXplore
Feb 13, 2023
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

XXplore

Brass Contributor

Feb 13, 2023

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

XLN00B
Copper Contributor
Feb 13, 2023
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 🙂