OFFSET function - column reference over the edge of the worksheet

Hi there,

I have a sum function with embedded offset function. My formula reference over the edge of the worksheet, thus, giving me an error message #Ref!. how do I put a constraint to be the maximum column A?

For example, I have this formula in cell J2: SUM(I2:OFFSET(I2,0,-$R$1+1)) where $R$1=10

Thanks

Regards,
Ronaldev

What range did you want that to evaluate to? You can change the formula to this:

SUM(I2:OFFSET(I2,0,-min($R$1,COLUMNS($A:I))+1))

Hope this helps.

Pete

Hi Pete_UK,
Thanks. The columns() is exactly what I am after. Awesome! My formula is now working.
Cheers,
Ronaldev