Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving data to cell according to date (2003 SP2)

    Editted: I just noticed some spurious letters that I did not put in!!

    Morning all

    This is not urgent or necessary so please don't waste any time if it is not easy to address.

    I have a holiday planner, on one ws is a user form that updates 4 fields on another ws - Name / DateFrom / DateTo / LeaveType can I in any way adjust the code behind the DateFrom field on the user form so that it appears on the other ws in line with the corrosponding month.

    For example, at the moment once the 4 fields are completed they update my 2008 ws from A7,B7,C7 and D7 through to A780,B780,C780 and D780 finding the next available blank row.

    Could I somehow amend the code to read something like date from = dd/mm/yy IF(mm=01,2008!find first empty row from A7onwards), IF(mm=02,2008!find first empty row from A72 onwards), IF(mm=03,2008!find first empty row from A137 onwards) etc. There are 65 rows for each month and they will never all be completed for one month so there would not be an issue of space per month.

    Hope that makes sense

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving data to cell according to date (2003 SP2)

    Hi Steve,

    Unless your sheet is set up with a specific structure, would it not be easier to simply sort the data once the new record is entered. Currently you say it is entered in the next black record at the bottom of the list. Why not leave it that way and then have the form sort the list when you close it. The sort will arrange the dates into date order which ultimately is what you want.

    My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> - <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rudi

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving data to cell according to date (2003 SP2)

    Hi Rudi

    Thanks for the advice, it was more for a visual thing really so that you could look to the left of the January entries and see the corrosponding data etc. as per this simplified screen shot I knocked up.

    As I said it was not important, anyway nice to see you around again

    Cheers

    Steve
    Attached Images Attached Images
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Moving data to cell according to date (2003 SP2)

    Nice to be back!!! TX

    I can see that your ws is not a conventional list. This will take a bit more than a sort to correct. It will probably take me a days worth of leave to help you on this one. As I am still at work I will need to leave it over to Hans (or Excel Guru Steve) or Pieterse (just to name a few) who seem to be on permanent leave to assist with queries like this one. <img src=/S/yep.gif border=0 alt=yep width=15 height=15>
    Regards,
    Rudi

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving data to cell according to date (2003 SP2)

    Cheers Rudi

    Its not really worth anyones effort, I just thought that if it was an easy ask it would be nice to have

    Cheers

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Moving data to cell according to date (2003 SP2)

    You can name each "top row" and then check the date in the form and start from that named range to "xlup" instead of the last cell in the sheet. My concern would be the small number of rows you are allowing. In months like November/December where everyone may take a vacation, you would run into a problem if someone took more than 1 "range of vacation dates" since you are only allowing for 1, since there is only 1 row per name.

    I know for this year, everyone in our group is taking the days between Xmas and New years off and I also took every Friday in December off. In the scheme you proposed, there would not be enough in December (though I suppose since December is the last month, you can extend as far as you like)

    But November would also be an issue. I also took all Fridays in November off (so I alone would need 4 rows) and many people took days here and there for deer hunting and November would have no room to expand...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •