help with formula for last input in column

M

mag

I would appreciate if someone can help me with this

In first column I will type date in some free format

1.01.2012.
3.01.2012
10.01.12.
11.01.11
blank
blank
blank
....

in one cell at the end of table I would like to have the formula that
will show me or will copy last input (date, string or cell value) in
column or defined range (for instance: A5:A35)

is it possible to use formula in one cell for this in Excel 2007

thanks in advance

DMag
 
M

mag

I forgot to write:

I solved that on this way, but would like some shorter formula


=IF(A49<>0;A49;IF(A48<>0;A48;IF(A47<>0;A47;IF(A46<>0;A46;IF(A45<>0;A45;IF(A44<>0;A44;IF(A43<>0;A43;IF(A42<>0;A42;IF(A41<>0;A41;IF(A40<>0;A40;IF(A39<>0;A39;IF(A38<>0;A38;IF(A37<>0;A37;IF(A36<>0;A36;IF(A35<>0;A35;IF(A34<>0;A34;IF(A33<>0;A33;IF(A32<>0;A32;IF(A31<>0;A31;IF(A30<>0;A30;IF(A29<>0;A29;IF(A28<>0;A28;IF(A27<>0;A27;IF(A26<>0;A26;IF(A25<>0;A25;IF(A24<>0;A24;IF(A23<>0;A23;IF(A22<>0;A22;IF(A21<>0;A21;IF(A20<>0;A20;IF(A19<>0;A19;IF(A18<>0;A18;IF(A17<>0;A17;IF(A16<>0;A16;IF(A15<>0;A15;IF(A14<>0;A14;IF(A13<>0;A13;IF(A12<>0;A12;IF(A11<>0;A11;IF(A10<>0;A10;IF(A9<>0;A9;IF(A8<>0;A8;IF(A7<>0;A7;IF(A6<>0;A6;IF(A5<>0;A5;IF(A4<>0;A4;IF(A3<>0;A3;IF(A2<>0;A2;))))))))))))))))))))))))))))))))))))))))))))))))
 
R

Ron Rosenfeld

I would appreciate if someone can help me with this

In first column I will type date in some free format

1.01.2012.
3.01.2012
10.01.12.
11.01.11
blank
blank
blank
...

in one cell at the end of table I would like to have the formula that
will show me or will copy last input (date, string or cell value) in
column or defined range (for instance: A5:A35)

is it possible to use formula in one cell for this in Excel 2007

thanks in advance

DMag


For the last entry in Column A:

=LOOKUP(2,1/(LEN($A:$A)>1),$A:$A)

For the last entry in a defined range, substitute that range for A:A above:

=LOOKUP(2,1/(LEN($A1:$A60)>1),$A1:$A60)
 
P

Paul Hyett

For the last entry in Column A:

=LOOKUP(2,1/(LEN($A:$A)>1),$A:$A)

For the last entry in a defined range, substitute that range for A:A above:

=LOOKUP(2,1/(LEN($A1:$A60)>1),$A1:$A60)

I assume this would work for rows too, given appropriate modifications?
 
R

Ron Rosenfeld

I assume this would work for rows too, given appropriate modifications?

Yes, it works for both. And note that by changing the last argument, you can alter the data that gets returned.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top