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

    Excel 97 (Formula Assistance Again)

    I am reposting this message

    This spreadsheet 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
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 (Formula Assistance Again)

    Hi Kerry

    I remember the nice workbook <img src=/S/grin.gif border=0 alt=grin width=15 height=15>. But I can't reproduce (or even comprendi) the problem you mention. What do you mean by "the whole thing" and what "goes out of order"? If I use your SORT button after adding Buzz, Aldrin, I can't really spot a problem. Buzz appears before Carlin in Accruals and after Butler in each of the other sheets. No doubt there's more to it than that, but could you give an example of a specific addition that messes things up?

    Alan

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

    Re: Excel 97 (Formula Assistance Again)

    Hi Allen!

    Good to hear from you again. Sadly my talents do not lie in describing my problem.

    Try this.

    Note that Allen and Avis have 6.00 and 1.00 hours accumulated total of leave in April. This comes from the April Worksheet.

    Now add a new person - call him Bill Abdul. Then run the sort macro using the button. Abdul gets Allens hours and Allen gets Avis' hours in the April column. See?

    Let me know how you go.

    Kerry

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 (Formula Assistance Again)

    Hmmm... I can see the problem now, but nothing jumps out at me. I'll have a ponder over it. Probably (hopefully) one of the experts will have picked it up by the time I get it sorted. I just noticed a "stray" entry in C23 of April 03. I don't think that's anything to do with this though.

    Alan

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

    Re: Excel 97 (Formula Assistance Again)

    Glad to see you can replicate my problem. Thanks for taking a look. That stray entry makes no difference. It was a left over I missed when clearing out the real data to send a sample.

    I certainly hope this problem can be resolved somehow, otherwise the whole thing is useless.

    Kerry

  6. #6
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Excel 97 (Formula Assistance Again)

    Kerry,

    I hope you didn't pay for this code <g>...

    You are sorting on columns: "AK", "AI" and "AJ" on the month sheets.
    I believe you want to sort by name?
    Suggest you change the "Key" assignments to the same as for the 'Accruals' sheet.

    Regards
    Jim Cone
    San Francisco, CA

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

    Re: Excel 97 (Formula Assistance Again)

    The Accruals sheet is to be sorted by surname.

    The Monthly worksheets are to be Grouped/sorted by Sector - Location - Position.

    This is so that we can ensure that in a sector we dont have for example all the admin people away at the same time.

    No I didnt pay - All the help I get is from kindness of this group and always appreciated.

    Thankyou for your reply

    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: Excel 97 (Formula Assistance Again)

    You didn't like my suggestions in your original post?

    Major Revamp (recommended): <post#=297001>post 297001</post#>

    minor changes with Macro solution: <post#=297056>post 297056</post#>

    What type of solution are you looking for, You never responded to the second until reposting the question.

    Steve

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 (Formula Assistance Again)

    Without a major redesign of the workbook like Steve suggested, the best way that I see to solve this problem would be to:

    1- Remove all of the formula from cells A4:B53 on all of the sheets.

    2- Change the button from "Sort Sheets" to "Add Name".

    3- Change the formula in columns AI:AK on the detail sheets to use VLOOPUP.

    4- Connect this button to a new macro that would use a User Form to get the first and last name and data for columns C:I on the Accruals sheet. The macro would then put the First and Last name in the first unused row on all of the worksheets, and the other data in columns C:I on the Accruals sheet, and then run the SortSheets macro to sort the sheets.

    That should solve the problem you are asking about. However, I also see another problem in this workbook that you may not have noticed yet. Your existing VLOOKUP formula do the lookup on the last name only. This is going to fail when you have two people with the same last name (like the two Butlers you currently have). It would be best to use something unique like an employee number or Social Security number for those VLOOKUPS. If that is not possible, you could add hidden columns on all sheets that concatenate the last and first names, and use that in the VLOOKUP. You would then have to hope you never get two people with the same first and last name.

    Will this get you started on a solution?
    Legare Coleman

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

    Re: Excel 97 (Formula Assistance Again)

    Hello Legare

    Thanks for taking a look. I know about the duplicate name problem and I am confident enough to fix that using your solution.

    I would really like to implement your ideas, but I will need some help.

    Point 1 and 2 - Done!

    3- Change the formula in columns AI:AK on the detail sheets to use VLOOPUP. - What would the Lookup formula be here?

    I need major assistance with Point 4.

    Look forward to hearing back.

    Kerry

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 (Formula Assistance Again)

    It will take quite abit of time to come up with more help on this, and I am a bit short on time at the moment. I will work on this as I have time available and get back to you.
    Legare Coleman

  12. #12
    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: Excel 97 (Formula Assistance Again)

    In accruals sheet insert a new column C
    In C enter:
    =+A5&B5
    This will combine last and first names
    Insert -name - define
    Name: Info
    Refers to:=Accruals!$C$5:$F$12

    In all the "month sheets" in cells
    AI2s enter 3
    In AJ2s enter 2
    In AK2s enter 4

    These are the column numbers for VLOOKUP

    In the month sheets in AI5 enter:
    =VLOOKUP($A5&$B5,Info,AI$2)
    Copy down rows and also into columns AJ and AK

    Now you lookup BASED on the name in A&B

    For part 4, this is no a simple how to, some general overview. (you might want to find a book with VB and Userforms)
    Goto VB, insert userform
    add text boxes for first and last name
    add a combo boxes for location, positon, sector (I assume you want a pulldown for selecting set items)
    Add labels to let the user know what they are for
    add an OK command button

    Create the code, to call the userfrom and then when acknowledged <OK>
    the code will add the name and items to next avail row in accruals,
    will add the names into all the month sheets,
    the macro will expand the range named INFO to get the new names
    then it will call the sort macro you created.

    I suggest playing and posting with specific questions and problems.
    Steve

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel 97 (Formula Assistance Again)

    See if the attached workbook will get you closer to what you need. I have changed most of the formulas and have changed the button to execute a new macro called AddName. This macro gets the new name using two InputBoxes, and put the name in all of the appropriate worksheets. I also added a new hidden column C to the worksheets that is the concatenation of the last and first names, and use that in all of the lookup formula. I do not have time available to implement this with a user form like it should be. This resilts in the monthly sheets not being sorted properly after the new name is added since the Location, Position, and Sector have not been entered yet when the sort is done. The complete solution would be to get all of the information for the new name using a User Form.
    Legare Coleman

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

    Re: Excel 97 (Formula Assistance Again)

    Thanks Steve

    I will work through this over the next few days.

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

    Re: Excel 97 (Formula Assistance Again)

    Thankyou Legare

    I will take a look at this over then next couple of days. I have a bit to work through between yours and Steve's suggestions.

    Kerry

Page 1 of 4 123 ... LastLast

Posting Permissions

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