OFFSET - funtion

Hello to all,

I have a data table with one row (but will have many others). Within that table I have a column with empty Field. As I input data from left to right, I want the empty field to dynamically fill in the last entry.

OFFSET(I1,3,0,1,1)

After reading on the web, I came across a function that can meet my needs but have not been able to make it work. Hopefully some one can guide me.

I have attached an example spreadsheet with my example of the offset function.

The problem is that you need to count then number of filled cells in the row that you are in, and this gives a circular reference.

You could turn iterative calc on> Files>Options>Formulas>Calculation options, and check the Enable iterative calculation checkbox, then use this formula

=OFFSET($A2,0,COUNTA(2:2)-1,1,1)

The 3 as 2nd argument is supposed to be the number of entries below I1? Does that mean you want this formula to return the bottommost entry in column I no lower than the row this formula would be in? If so, the formula for row 9 could be =LOOKUP(1,0/(1-ISBLANK(I$2:i9)),I$2:I9) . If that's not what you're after, please provide more details. There's almost certainly a better approach than using OFFSET.

There's almost never a good reason to use OFFSET. There's usually a way to use INDEX() or INDEX():INDEX() instead. OFFSET is a volatile function. If you use it in a lot of formulas, you can really slow down recalculation.

Originally Posted by Ace_XL

Maybe in Cell B2 (non-array formula)

=LOOKUP(2,1/(C2:XFD2<>""),C2:XFD2)

Copy down

OP's workbook doesn't show OP wants to work with multiple columns. Why would this make any sense?

Wow. Never thought what I thought was simple is a high level. . Looks Like I need to look and analyse for a better understanding of what is being used. Thank thank you. Will test all suggestion..... HAPPY NEW YEAR. p.S. Any suggestion on books that explains the above I can buy? Thank you again.