help with formula for last input in column
 

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

help with formula for last input in column
 

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;I F(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;A 32;IF(A31<0;A31;IF(A30<0;A30;IF(A29<0;A29;IF(A2 8<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;I F(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;))))))))))))))))))))))))))))))))))))))))))) )))))


On 13.1.2012 12:45, mag wrote:

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

help with formula for last input in column
 

if you want to know the last row
=MATCH(9999999,a:a)
if you want the last value
=INDEX(B:B,MATCH(9999999,B:B))
Post back if this is NOT what you want.

On Jan 13, 5:45*am, mag wrote:
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

help with formula for last input in column
 

Hi

You will have to give a clearer explanation as to what it is you want.

I can only surmise from your formula that if Range("A2:A49") < 0 then
they equal their respective cells value.

I'm a little confused as to what it is you want copied or shown as the
last input.

Also the Date ( free format ) is a little vague, is the cell format
going to be ( Text, General or Date ).

HTH
Mick



On 13/01/2012 11:30 PM, mag wrote:

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;I F(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;A 32;IF(A31<0;A31;IF(A30<0;A30;IF(A29<0;A29;IF(A2 8<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;I F(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;))))))))))))))))))))))))))))))))))))))))))) )))))



On 13.1.2012 12:45, mag wrote:

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

help with formula for last input in column
 

On Fri, 13 Jan 2012 12:45:22 +0100, mag wrote:


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

Answered in another group:

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

help with formula for last input in column
 

hi Dmag,

=INDEX(A1:A100,MAX(IF(A1:A100<"",ROW(A1:A100))))
array formula to be validated with


--
isabelle

help with formula for last input in column
 

array formula to be validated with ctrl + maj + enter

--
isabelle


Le 2012-01-13 09:34, isabelle a écrit :
hi Dmag,

=INDEX(A1:A100,MAX(IF(A1:A100<"",ROW(A1:A100))))
array formula to be validated with

help with formula for last input in column
 

Thank you for your answers.

I used Ron's formula and it works.

God bless those who knows what is internet useful for and all that share
their knowledge using internet.

:-)

On 13.1.2012. 13:30, mag wrote:

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;I F(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;A 32;IF(A31<0;A31;IF(A30<0;A30;IF(A29<0;A29;IF(A2 8<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;I F(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;))))))))))))))))))))))))))))))))))))))))))) )))))



On 13.1.2012 12:45, mag wrote:

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