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
Click to expand...


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)
Click to expand...

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?
Click to expand...

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