| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| inverse of the column function? i.e. input a number, output thecorresponding column text label | Excel Worksheet Functions | |||
| INPUT BOX FIND NAME IN COLUMN A | Excel Discussion (Misc queries) | |||
| Output in Column A based on Input in Column B | Excel Worksheet Functions | |||
| input to 2nd column help | Excel Discussion (Misc queries) | |||
| Autofill Column D based on input in Column C | Excel Discussion (Misc queries) | |||
Linear Mode
