Return data to the left based on next blank cell

[dannywhite]

Hi All,
Im sure there is an easy way to do this, after a fair bit of googling nothing seems to be fit what i am after hence the post.

I have weekly incremental data been entered into rows, i would like to return the last entry into another cell.

E.g

my formula would be in A1
the data
B1, C1, D1, E1 etc
this would grow
therefore i would like a formula to return the last value entered, the above case it would be E1, as the dataset grows i want A1 to automatically take the last entered value.

i was thinking of somehow searching the next blank cell and return the value to the left as a solution?

Welcome anyone help.

Cheers
Danny

 

[skywriter]

Hi Danny,

You could try this formula in A1, it will get you to row A1000.
I'm not a big formula guy, I like writing code, but it works and maybe by posting this one of the formula gurus will come along and post something more efficient.

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

Good Luck

 

[FlameRetired]

Another approach.

=LOOKUP(2,1/(B1:ZZ1<>""),B1:ZZ1)

Just be sure to use a suitably large enough column reference to include future data and minimize the need for future editing. Try to avoid the temptation to reference whole rows though.

 

[skywriter]

Thanks Dave,

I guess I've got foggy head today.

Somehow I interpreted C1, D1 etc as going down column A.

 

[FlameRetired]

Been there done that. Maybe something to do with the week-end. LOL

 

[MandeepBaluja]

=index(b1:zz1,counta(b1:zz1))

 

[Peter_SSs]

=index(b1:zz1,counta(b1:zz1))

I'd say that is somewhat risky as any empty cells among the data will cause it to return an incorrect result without warning.