Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    Okay, here goes. Wasn't sure if this should be posted here or in the one for spreadsheets. You guys have ALWAYS come through for me before so I'm hoping this request is do-able. It would be MAJOR for me. I'm sitting at my desk manually entering data into yearly contracts which have been created in Excel. (copy attached) You will see there are several formulas to make life easier. The numbers that create the contract costs are in a block off to the side, as you can see. As my boss's business covers four counties with different sales tax, the formulas need to be different, obviously. Also, there are a number of tax exempt properties in all four counties. So I have created 16 or 17 different Excel files. My question is, would it be possible to enter this information into an Access database or Excel spreadsheet and merge into the contracts? Even if the figures/costs change from year to year, it would at least save having to manually enter over 800 names and addresses onto each contract. This year the costs didn't change, so it would have saved MUCH time had it been done last year. Any other suggestions, please let me know. Crossing my fingers! Thanks!
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Actually, it would be possible to do the entire thing in Access if you chose to. Or you could put just the property/contact info into an Access database, and then use Automation from Access to create each Excel file from an Excel template. That way you could put the cost data for each year into a table, and do the costs as well. It would be a good sized development effort, but if you have all of 2010 to do it, the time savings in 2011 would be substantial.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Sep 2001
    Posts
    211
    Thanks
    32
    Thanked 0 Times in 0 Posts
    I think I would keep the Excel files since they are already set up. In the past I have only set up very simple tables and queries in Access so, to start, would I set up a table with the variables (names, addresses, costs)? I need to take just one step at a time since I'm not familiar with a lot of the features in Access.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    That would be my recommendation as well if you haven't worked much with Access? Are you somewhat familiar with VBA (Visual Basic for Applications)? If you were going to do any automation, you would need that background first. And you would need to understand the object model for both Excel and Access at least to some degree. I presume right now you have an Excel workbook for each client for each year they have used the service. I would start with a Customer/Client table that had basic information such as an ID number, name, service address, billing address (if different), and some fields to indicate pricing variations such as the senior discount. Then I would set up a second table to provide the different options a client might choose. Finally, I wouild create a table that had the ID number, a year field, and the selection of services that the client chose that year. That would be a start. Getting the data from your workbooks would be the next challenge. Then you would want to create some forms to simplify the editing of the data.

    Beyond that, you start getting into tracking pricing by year, tracking payments, scheduling applications, tracking applications, etc. It would also be possible to write VBA code to populate the tables from the workbooks you already have. Your current data isn't what the database world calls normalized, but as long as your cells in the workbooks are used consistently, and if you have a consistent naming convention for the workbooks, you should be able to acomplish that task in a fairly straightforward manner. Post back if something doesn't make sense to you.
    Wendell

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    It seems that you could save a lot of time by using what you have and creating a couple of lookup tables in a linked sheet/workbook.
    Then replace the formulas in the contract with vlookup's or hlookup's depending on the table setup. Of course you would have to designate cells in the contract worksheet to identify things like county, tax status, etc. and use these references in the lookup formulas.
    Also investigate the use of the indirect() function to grab the values in these designated cells and plant them in the lookup formulas.
    I don't have enough information from your example to know if the pricing is fixed or if you need a table for this based on the size of the property but this is also doable in the same fashion.

    I've included a file I used to teach a course. Check out the Data tab and the formula for Salary. Hopefully this will better explain what I tried to say above.[attachment=88094:Course Cost Estimator - Audit Precedents.XLS]

    Good luck.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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