Results 1 to 7 of 7
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Troubleshooting Techniques (2003/SP2)

    In post <post#=679,036>post 679,036</post#>, I was asking about comments in a spreadsheet.
    I'm wondering how others have approached the problem of identifying errors in a spreadsheet.
    I'm attaching a sketch of the spreadsheet design, that I'm working with (it's pretty standard really). Basically the users have ignored the links, unprotected the sheets and edited each students' sheet individually. Along the way, they have deleted tasks (all students are supposed to have the same list of tasks) and made the final summary sheet (not shown in the sketch) kind of meaningless, since different tasks are now being totaled together.
    In my original spreadsheet design, each sheet had basically the same layout. In other words Task 1.1 appeared in cell A4, in every sheet.
    Since the users didn't edit every cell, I needed to find a way of marking which cells didn't match the plan.
    Using VBA I looped through each column, examined the each cell, and flagged those cells which didn't contain the expected linking formula.
    This allowed me to zero in and ( <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15> ), manually fix each problem. As they had been quite creative in causing the problems I couldn't think of a way of automating the solutions, (that wouldn't take longer than a manual fix).

    I'm sure many of you have been in a similar situation, and I am curious as to what kind of techniques you have used to identify and fix problems.
    Attached Images Attached Images
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  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: Troubleshooting Techniques (2003/SP2)

    I think the best method is prevention of the problems to begin with.

    From a "design" standpoint, I am not a proponent of a collection of similarly formatted sheets. I prefer to combine them and create a master list with them all on one sheet and an additional column to indicate which "sheet" it is on. This allows using the builtin autofilter with subtotals and pivot tables to summarize.

    As to users:
    I have used protection (with no password) to prevent accidental mistakes. I will use passwords if it is important to protect the data from more "purposeful changes"

    Data integrety may also be enhanced by creating an entry form which saves the data into the appropriate format if the users seems to unable to adapt to a format required for storage (this may mandate using a program which "controls" the users better and has more builtin controls, eg Access)

    As to fixes once they occur, I don't think there are too many generic answers. It will depend on what caused the problems and why they occured. Whether they are accidental or on purpose, it suggests a "design-flaw" or "disconnect" with the user. If it is only one user, it might have to be resolved with him/her. If it is multiple users, you may have to work with them to help design a better interface.

    As a user of many computer programs written by company personnel and a person who dabbles in programming, I see many issues with programs written in the easiest manner with no regard to the user and how they will interact with it. Making a good user-interface (to not only prevent user changes to design and maintain data integrity, but to make it more painless for the user to use) adds a lot to effort both upfront in design and in any coding/creation.

    Steve

  3. #3
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Troubleshooting Techniques (2003/SP2)

    How true - it certainly was a disconnect with the user. My user, who was originally the only person who was going to use these files was so thrilled by how they worked he distributed them around (with, I can only presume faulty instruction) and then left the department.

    I felt the multi sheet approach worked well for my original user - it worked along the lines of how he visualized the data (one sheet per student). Anyhow, after fixing their existing data I doubt the budget will cover a redesign although I hope that training is in their futures.

    Prevention is the best medicine true, but surely you've been faced with a repair job on occasion? Any techniques you found handy?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  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: Troubleshooting Techniques (2003/SP2)

    It depends on how bad it is. If it is really bad I often start with my original and then just extract the new "data" from the modified one.

    In other cases I might try to discover what got damaged. Most I do manually, perhaps using some formulas to check results.
    I will also use the "auditing tools" to make sure the formulas point to where they are supposed to.

    On occasions I will use code to mark things (as you suggest).

    Troubleshooting can be just as my "art" as science...

    Steve

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

    Re: Troubleshooting Techniques (2003/SP2)

    My approach would be to fire any employee who unprotects the worksheet or fail any student who unprotects it, depending on which is doing it. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Legare Coleman

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Troubleshooting Techniques (2003/SP2)

    I've imagining using a cluebat <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Troubleshooting Techniques (2003/SP2)

    Unfortunately, in the USA that would probably result in a billion dollar lawsuit! Our legal system dosen't have a clue.
    Legare Coleman

Posting Permissions

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