Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Sort Not working (Excel 97/2000)

    Hello helpers

    My problem is difficult to explain, so I have attached the actual spreadsheet so that you can see what I am talking about.

    My Spreadsheet is designed to keep a tally of staff and their Recreation leave entitlements and also to keep record of what leave they plan to take each month. This doubles as a roster system so that at a glance, managers can see who has requested leave in a particular month in a location. Thus ensuring adequate staffing in a location at any one time.

    In my spreadsheet I have a main sheet (accruals) with links to sheets for each month of the year. (only 3 months at this stage) There are also links in each monthly worksheet back to the main sheet (Accruals).

    The trouble I have is that when I sort the "Accruals" Worksheet by Location. The sort works for the linked cells, but the cells for each day of the month dont move and thus the data is in accurate.

    What am I doing wrong?

    Kerry

  2. #2
    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: Sort Not working (Excel 97/2000)

    You could just add a blank column between I and J and hide it. Then when you sort the date columns (>K) will not be sorted.

    Steve

  3. #3
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort Not working (Excel 97/2000)

    It is the Apr May Sheets that are the problem. I want the data in the cells that are the days of the month 1 through 30 to move with the person, when I sort the Accruals worksheet by Location.

    As it is now you can see the person Valma Ahnge at Cell C3 has 7.5 hrs in the April sheet. Go to the Accruals Sheet and Sort by Location and then come back and you will see that she moves, but the 7.5 hours are now next to Brian Dew.

    Does this make more sense?

    Kerry

  4. #4
    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: Sort Not working (Excel 97/2000)

    Don't link the names and locations on those sheets to the accrural sheets. You are rearranging the names and locations with formulas. but NOT doing anything to the other columns. keep them all "static" I would use a COMBINED name. Then use VLOKKUP to locate the name on that row in ACCRUAL to get the value of interest from that table instead of looking in a stagnant spot.

    Steve

  5. #5
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort Not working (Excel 97/2000)

    Ok Steve, I will give that a go. It sounds quite sensible. Thankyou.

    Unfortunately I will have to wait till morning to do it as its midnight now and I am brain dead!

    Will let you know how it turns out.

    Thanks!

    Kerry

  6. #6
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort Not working (Excel 97/2000)

    Sadly I have to admit, after giving Steves suggestion a go, I have failed and have absolutely no idea how to do this.

    HELP! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Not working (Excel 97/2000)

    Steve will no doubt react, but here is a modified workbook. The names and locations in the April and May worksheets are static (not formulas), so they won't be sorted with the Accruals worksheet.
    The totals in the Accrual worksheet are taken from the April and May worksheets by means of VLookup formulas; in my version they look up the last name only, which is dangerous if you have multiple persons with the same last name; it would be safer to use a unique identier like Employee Number or SSN.

  8. #8
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort Not working (Excel 97/2000)

    Thankyou for your response Hans! Sorry for my delay in responding. I have been absorbing what you have done.
    It is doing what I wanted except for one thing, which I may have to accept.

    When I complete this spreadsheet, it will have a full 12 months in the Accruals and a sheet for each of those 12 months. If I get a new staff person, I will have to add them individually to each monthly sheet.

    Is there a way around this? Perhaps a macro?

    Once again thankyou.
    Kerry

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Not working (Excel 97/2000)

    If you select the Accruals and month worksheets (click the Accruals tab, Shift+click the last month tab), you can enter data in all selected sheets at once. This will work fine for the first and last names, since they are in the same location on all sheets. For the location and position, you can enter in all twelve months at once, but you'll have to do Accruals separately. Don't forget to cancel the group selection (right click a tab for this option). The formulas can simply be copied down.

    A macro would be possible too: enter the relevant data in one sheet, for instance the Accruals sheet, and use a macro to copy them to the other sheets. If it doesn't occur too often, I would probably stick to the manual procedure described above.

  10. #10
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort Not working (Excel 97/2000)

    Hey that's nifty! I didnt know about that technique. I've learnt something new. Thanks Hans!

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort Not working (Excel 97/2000)

    If you're willing to learn something more just before Easter: selecting sheet tabs works like selecting files in Windows Explorer.

    Click, then Shift+Click selects a contiguous range.

    Click, then Ctrl+Click allows you to select and deselect individual sheet tabs.

  12. #12
    4 Star Lounger
    Join Date
    Apr 2002
    Location
    Northern Territory, Australia
    Posts
    471
    Thanks
    16
    Thanked 0 Times in 0 Posts

    Re: Sort Not working (Excel 97/2000)

    Excellent Hans! I am always willing to learn new tricks. It makes me look clever to my boss and one day they may think I am clever enough to pay me more!

    Happy Easter

    Kerry

Posting Permissions

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