Excel Offset/Count Command 2

[Sopman]

Somedays have data in cells A1,A2,A3,A4,A5,A6
Somedays I have data in A1,A2
Somedays I have data in A1,A2,A3,A4
I'll always have data in A1 and as more data comes in it will go in the next cell, but the most data I will have will be in A1:A6. The least could be just in A1. I never know which row is the last for data.

I would like to have the data written in the last cell automatically put that number into cell B5.

This formula did work until I put formulas in A1:A6 and now it only works when I have a number in A6.
=OFFSET($A$1,COUNTA($A:$A)-1,0,1,1)

Is there a way for it to not see the formulas?

Sopman

 

[KenWright]

Are your numbers always greater than 0, or is 0 an option as a number. If always greater than 0 where they exist then

=OFFSET($A$1,COUNTIF($A$1:$A$6,">0")-1,0)

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Failing that, another option that will likely cover all eventualities is

=LOOKUP(99^99,A1:A6)

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Hi,

Do you have ANYTHING in column A, BELOW row 6? If so, that's a problem.
[tt]
The Function states...

Start with A1

offset COUNTA(A:A) rows from ROW 1 MINUS 1

offset ZERO columns from COLUMN A

Range ONE ROW high

Range ONE COLUMN wide
[/tt]
that range is what your function is returning.


Skip,

_[red][/red]

 

[Sopman]

Thank you for your replies.
Ken, that works, because all numbers are >0.
Skip, thanks for the explanation. I'll print that out for future reference.

Sopman

 

[SkipVought]

COUNTA works REGARDLESS of the contents of a cell.

Skip,

_[red][/red]

 

[KenWright]

Trouble is though, that is the very reason why I'm guessing it doesn't work for what he is doing. I'm guessing he has formulas in each cell that either return a value or "" depending on whatever the criteria is.

I think he needs "" to be treated as blank for COUNT purposes, but COUNTA will still count that cell as containing something, and therefore pass the 'wrong' value to the argument in OFFSET for what he wants.

As long as the data is always numeric or "" then he could probably just substitute COUNT for COUNTA and that will work just as effectively as it will ignore the ""s.

Regards
Ken............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[Rofeu]

Hi,

you can enter the following as an array-formula:

=OFFSET(A1,0,LARGE(COLUMN(A1:F1)*--(A1:F1<>""),1)-1)

This will work regardless if there are only numeric values and not count the ""'s.

Cheers,

Roel