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

    Formula assistance (Excel 97/2000)

    I have had a lot of help from this forum with this spreadsheet. It summarises the leave records of staff within our department. It has been used for a few months without a problem, until we had a new staff member to be added and then it fell apart.

    The problem is that when you add a new person in the next vacant cell on the Accruals Sheet and run the sort macro the whole thing goes out of order. The records seem to slip out of order because of adding another record. I guess the way to describe it is that it is no longer relative.

    Any help would be appreciated.

    Regards
    Kerry

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

    Re: Formula assistance (Excel 97/2000)

    You posted this in the Access forum. Shouldn't it be in Excel? If so, I can move it for you.

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

    Re: Formula assistance (Excel 97/2000)

    Thanks Hans that would be good. Sorry.

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

    Re: Formula assistance (Excel 97/2000)

    Here is the attachment that is supposed to go with it.
    Attached Files Attached Files

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

    Re: Formula assistance (Excel 97/2000)

    Done.

  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: Formula assistance (Excel 97/2000)

    I would recommend a revamp of your spreadsheet.
    I am NOT a fan (I actively DISCOURAGE) have multiple worksheets with the IDENTICAL setup. It is always better to group them into 1 sheet. This allows using filtering (with subtotals) and/or pivot tables to get summary info.

    A sheet with the:
    DATE
    NAME
    "Entry" (any info you would be entering in the date column on a particular month now)
    Other info for that date?
    Optional calc for month&year
    Optional calc for year

    Now you can filter on the year to see items, month/year, or even date. Also can filter on a person to see his/her info.

    Steve

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

    Re: Formula assistance (Excel 97/2000)

    Unfortunately I am not in a position to completely revamp the thing now. Surely there is a way to resolve this problem without starting again!

    I take your advise for any future projects.

    Kerry

  8. #8
    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: Formula assistance (Excel 97/2000)

    On each of the other sheets, do NOT reference a cell if you plan on sorting the cell you reference!
    hard-code all the names in the "month sheets" since you want the ROWS to be linked to that name and NOT the particular row (as you are doing now).
    Before you call the sort macro, get any NEW names that have been added, and put them into the columns of all the sheets (the "sort button" could call this routine)

    You could check for "missing names" on each sheet and then add any "new ones" OR
    you could just copy the entire column from the first sheet and then loop thru all the other sheets. If you want to be ANAL, you could CHECK each sheet to make sure they are setup identically (just in case someone sorted one of the sheets differently), otherwise you could add the names in (on that sheet) are the wrong order.

    After adding the new names to all the sheets, the macro could call the sort macro you created.

    Steve

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

    Re: Formula assistance (Excel 97/2000)

    Reposted in <post#=297901>post 297901</post#>. All further replies there, please. I am locking this thread to avoid confusion and duplication.

Posting Permissions

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