penultimate cell non empty

[max_max]

Hello to all.
This formula:

=SE(A100>0;"devi aggiungere nuove righe";"")
= IF (A 100> 0, "you need to add new rows", "")

if last cell is empty it displays the message.
For the penultimate cell non empty you can edit?
A greeting.
max_max

[ChemistB]

Are you looking for the last non-empty cell in Column A? Or the next to last non-empty cell in Column A (A1:A100)?

Does Column A have numeric or text entries (or both)?

[max_max]

Last non-empty cell in column A.
Both values
max_max

[ChemistB]

Last Non-empty cell in Column A assuming both numbers and text are in A

=INDEX($A$1:$A$500), MAX(MATCH("zzzzz", $A$1:$A$500), MATCH( 9E99, $A$1:$A$500))

If it's just text, use just the zzzzz match. If it's just numbers, use the 9E99 match

[hemesh]

below will check last non empty cell with both text or numbers or only text or only numbers
=LOOKUP(2,1/(A1:A500<>""),A1:A500)

[max_max]

Hello to all.
Maybe I explained myself wrong.
This formula:

=SE(A100>0;"devi aggiungere nuove righe";"") '<<< italian
=IF(A 100>0,"you need to add new rows","") '<<< english

if last cell is empty it displays the message.
The change is only if a value is entered in the penultimate row not empty where to display the message "you need to add new rows".
The formula by inserting new rows should be updated by itself.
I hope I explained.
max max

[sandy666]

I assumed:
data: A1:A100
formula: B2 and down
=IF(AND(A1<>"",A2=""),"msg","")
or if you want data and formula in the same column, first you should turn on iterative calculation and (with data in A1) put formula into A2, and recalculate sheet, but this is not good solution.
or look for VBA solution

[XOR LX]

This:

=LOOKUP(2,1/(A1:A500<>""),A1:A500)

should only ever be used in place of this:

=INDEX($A$1:$A$500,MAX(MATCH("zzzzz",$A$1:$A$500),MATCH( 9E99,$A$1:$A$500))

(I corrected a typo in the range being passed to INDEX) if there may be null strings ("") present within the range, since the latter is otherwise much, much faster than the former (though unfortunately fails if null strings are present at the end of the range).

Actually, technically the double-MATCH set-up as given will also fail if there are either no numbers or no text entries within the range, so you should use something like:

=INDEX(A:A,AGGREGATE(14,6,MATCH(CHOOSE({1,2},"zzzzz",9E99),A:A),1))

to handle a MATCH error.

Regards

[max_max]

Hello,
in the attached file I tried with sandy666 solution but do not update if you add new rows.
I also tried other formulas posted but I could not run.
max_max

penultima cella.xls

[sandy666]

with your last example, try

Please Login or Register  to view this content.

but this is working with one column (in this case: A:A)

[max_max]

Hello sandy666,
error here:

(9.99999999999999E+307

Maybe you are missing a piece of the formula?
max_max

[sandy666]

replace to: 9999
I don't remember max numeric value in cell for EX 2007

EDIT:
Largest allowed positive number 9.99999999999999E+307
Applies To: Excel 2007
Excel specifications and limits
adapt my formula with ; not with ,

Italian version:

Please Login or Register  to view this content.

[max_max]

Also error in Italian.

[sandy666]

error with the same thing?
don't be so humble person and type more information

it's weird because I copied formula a few times to different places in your example file and it works every time.
I can't check italian version, I can translate only so you need to check where is my error
and can't check it in EX2007, sorry.

[max_max]

Error image

Appunti01.jpg

[sandy666]

check this one:
Attachment 446508

or check decimal point, not dot " . " but comma " , "

Please Login or Register  to view this content.

or replace: 9,99999999999999E+307 with 2^512 and there will be no problem with dot or comma

[sandy666]

[deleted] double post

[sandy666]

try:

Please Login or Register  to view this content.

Italian ver.

Please Login or Register  to view this content.

If you've last empty row in the range of data, formula will show nothing else "Add row"
hope it will work correctly

edit:
from the left and right side of table cannot be any numeric values. table should be alone in the worksheet but you need to check yourself how it works. I don't know how big table will be so I did it from start to the end of sheet.

Last row and column with numeric value - add row.xlsx

[max_max]

Hello sandy666.
I think is a good job.
I have to try on a more wokksheet, but it should be accurate.
Thank you.
A greeting.
max_max

[sandy666]

Glad to help/
If problem is resolved mark thread as solved. Thank you
Don't forget give a star if help was good

[sandy666]

I'm stubborn

here is formula for numeric and text values, but it's "memory eager" formula.

Please Login or Register  to view this content.

IMHO, better to use is formula for numeric values only OR for text values only.

That's all I think