Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic deletions
Is it possible for the contents of cells in A1-B1-C1 and D1 to automatically clear (without deleting the row) when the date in A1 is more than 60 days old? - and - have the contents in the next cells down, move up to the top. In other words, this is a Pass Log and entries over 60 days old are no longer needed. A1, B1, C1 and D1 in the following ,will no longer be an issue in October so those cells would clear in October and the entries currently in A2, B2, C3 and D2 would move up one row and remain there till they were 60 days old..... and so forth down the sheet. A B C D 1 8/31/06 5776 Jones Smith 2 9/22/06 5778 Brown Wallace Thanks Johnnie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic deletions
One way to achieve this ..
Source data assumed in sheet: X, cols A to D, from row1 down In another sheet: Y (say), In A1: =IF(X!A1="","",IF(X!A1+60<TODAY(),"",ROW())) In B1: =IF(ROW()COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMALL( $A:$A,ROW()),$A:$A,0))) Format B1 as date. Copy B1 to E1 Then just select A1:E1, copy down to cover the max expected extent of data in X (Hide away col A if desired) Y will return the required results. Expired lines ( 60 days old) will not show, and the rest will move up automatically. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ups.com... Is it possible for the contents of cells in A1-B1-C1 and D1 to automatically clear (without deleting the row) when the date in A1 is more than 60 days old? - and - have the contents in the next cells down, move up to the top. In other words, this is a Pass Log and entries over 60 days old are no longer needed. A1, B1, C1 and D1 in the following ,will no longer be an issue in October so those cells would clear in October and the entries currently in A2, B2, C3 and D2 would move up one row and remain there till they were 60 days old..... and so forth down the sheet. A B C D 1 8/31/06 5776 Jones Smith 2 9/22/06 5778 Brown Wallace Thanks Johnnie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic deletions
The foregoing assumes that the dates in X's col A are real dates recognized
by Excel, of course <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic deletions
Oops ... Line
Format B1 as date. Copy B1 to E1 should have read as Copy B1 to E1. Format B1 as date. (otherwise cols C to E will be in date format as well <g) btw, the desired results -- ie the source list in X, less the expired items --- are returned in Y's cols B to E -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic deletions
Max wrote: One way to achieve this .. Source data assumed in sheet: X, cols A to D, from row1 down In another sheet: Y (say), In A1: =IF(X!A1="","",IF(X!A1+60<TODAY(),"",ROW())) In B1: =IF(ROW()COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMALL( $A:$A,ROW()),$A:$A,0))) Format B1 as date. Copy B1 to E1 Then just select A1:E1, copy down to cover the max expected extent of data in X (Hide away col A if desired) Y will return the required results. Expired lines ( 60 days old) will not show, and the rest will move up automatically. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ups.com... Is it possible for the contents of cells in A1-B1-C1 and D1 to automatically clear (without deleting the row) when the date in A1 is more than 60 days old? - and - have the contents in the next cells down, move up to the top. In other words, this is a Pass Log and entries over 60 days old are no longer needed. A1, B1, C1 and D1 in the following ,will no longer be an issue in October so those cells would clear in October and the entries currently in A2, B2, C3 and D2 would move up one row and remain there till they were 60 days old..... and so forth down the sheet. A B C D 1 8/31/06 5776 Jones Smith 2 9/22/06 5778 Brown Wallace Thanks Johnnie Sorry I'm such a newbie. This is how I read your suggestion using my limited knowledge. <Source data assumed in sheet: X, cols A to D, from row1 down You assume sheet1 (called X here?) has entries in column A,B,C and D from rows 1 down. <In another sheet: Y (say), I should open another sheet - like sheet2? <In A1: =IF(X!A1="","",IF(X!A1+60<TODAY(),"",ROW())) I should paste that formula in cell A1, of sheet2. <In B1: =IF(ROW()COUNT($A:$A),"",INDEX(X!A:A,MATCH(SMALL( $A:$A,ROW()),$A:$A,0))) I should paste that formula in cell B1 of sheet2. <Format B1 as date. Copy B1 to E1 I should right click on B1, choose Format Cells and Number/Date. Then copy B1 to cell E1. <Then just select A1:E1, copy down to cover the max expected extent of data in X I'm not sure about this part - what to copy down. Thanks Johnnie |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic deletions
wrote
... The line .. Format B1 as date. Copy B1 to E1 was corrected in my 2nd follow up to: Copy B1 to E1. Format B1 as date. To copy B1 to E1, just drag the fill handle of B1 (point at the bottom right corner of B1, there'll be a solid black square - that's the fill handle) across to E1. Then to format B1, select B1, click Format Cells Date (Choose your desired date format) OK. .. Then just select A1:E1, copy down to cover the max expected extent of data in X I'm not sure about this part - what to copy down. "Select A1:E1" ... means you left click n highlight the range A1:E1, ".. copy down" ... means you then drag the fill handle of E1 down (with A1:E1 highlighted). The above basically means that we're copying at one go the formulas within A1:E1 down the cols. Anyway, here's a working sample construct for your easy reference: http://cjoint.com/?izhmTNhSIR Auto deletion of expired cases in new sht.xls (Link is good for 14 days) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic deletions
Max wrote: wrote .. The line .. Format B1 as date. Copy B1 to E1 was corrected in my 2nd follow up to: Copy B1 to E1. Format B1 as date. To copy B1 to E1, just drag the fill handle of B1 (point at the bottom right corner of B1, there'll be a solid black square - that's the fill handle) across to E1. Then to format B1, select B1, click Format Cells Date (Choose your desired date format) OK. .. Then just select A1:E1, copy down to cover the max expected extent of data in X I'm not sure about this part - what to copy down. "Select A1:E1" ... means you left click n highlight the range A1:E1, ".. copy down" ... means you then drag the fill handle of E1 down (with A1:E1 highlighted). The above basically means that we're copying at one go the formulas within A1:E1 down the cols. Anyway, here's a working sample construct for your easy reference: http://cjoint.com/?izhmTNhSIR Auto deletion of expired cases in new sht.xls (Link is good for 14 days) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Thanks so much. That cleared it up and it worked. You guys in here are an incredibly valuable asset to we who have brain damage. Someone else tried to help me with this early thurs morning.... I was going to work on his solution later in the afternoon but it was deleted from the post. Thanks to him, also, if he sees this. Johnnie www.digitalbirdcrap.com |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatic deletions
wrote
Thanks so much. That cleared it up and it worked. You guys in here are an incredibly valuable asset to we who have brain damage. You're welcome ! Thanks for the feedback .. Someone else tried to help me with this early thurs morning.... I was going to work on his solution later in the afternoon but it was deleted from the post. Thanks to him, also, if he sees this. I don't know who this chap might be, but I was also having some difficulty posting through using MSOffice Online initially <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatic and manual calculations | Excel Discussion (Misc queries) | |||
Automatic and manual calculations | Excel Discussion (Misc queries) | |||
enable automatic refresh | Excel Worksheet Functions | |||
Please help with adding automatic data. | Excel Discussion (Misc queries) | |||
options to update automatic links | Excel Worksheet Functions |