Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2013
    Posts
    82

    Unanswered: Customer Info From Invoice

    Is there a way to save the customer information from my invoice to a table in another worksheet contained in my workbook?

    I have a macro to clear the data with a new invoice number when needing a new invoice but still want to save the customer information if needed for another date.
    Attached Thumbnails Attached Thumbnails Customer Info Invoice.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Thats data, in my view it belongs in a database, not a spread sheet.
    you can pull data from a database INTO a spreadsheet (if you must) by querying the data

    Over the years Ive had a lot of grief with people modifying spreadsheets, either inadvertently or deliberately and breaking the spreadsheet. store original data in a database and let your users do whatever the heck they like with it in spreadsheets.... but dont' let users play with original data in a spreadsheets... its a recipie for disaster.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2013
    Posts
    82
    Yes I realize that a database is a better way to go. Unfortunately the one user in our company does not have Access on his computer and all he needs is invoices. I just thought it would be useful for him to store the customer's information.

    If I create a Customer Table on Sheet 2 with a Customer ID. Could that ID be selected and fill out the cells for information on that customer?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    but he does have the means to send and retrieve data from an Access DB (well strictly a JET DB), even if he doesn't have a copy of Access itself. furthermore if there is need you could always deploy an Access application using the free (well free for Access 2004 on) runtime environment
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2013
    Posts
    82
    His computer is not on our network so he can't do that.

    I am sorry to be trouble. Is there a way to fill the fields on sheet 1 with the ID selected from the table in sheet 2?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    using VBA to do that, however My Excel VBA skills are pretty rusty
    however Excel does have one neat trick, and that is macro recording.
    what I tend to do in Excel is start macro recording, do what you want the code to do, then stop macro recording

    save the macro and then edit it to make it generic.

    However you may be better off taking this query to a forum that specialises in Excel, rather than a forum which is more about data and databases
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Aug 2013
    Posts
    82
    Okay I will try another forum. Sorry about posting here.

    I do appreciate all of the help this forum has provided me for programming my database.

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    I have a couple of workbooks that do the reverse - they take a flat table of data, and display it one record at a time for printing and processing. If your user has an editable invoice sheet and wants to save what he's done, you have the following work to do:
    • Map the editable invoice sheet to one or more flat tables of data on other sheets in the file
    • Come up with a means of establishing the final populated row(s) of your data table(s)
    • Copy the data from the editable sheet to the storage sheets (optionally unprotecting and protecting them as you go)
    • Blank the editable cells once the transfer is complete

    You might also want to look at a means of displaying past invoices on another sheet, or giving him the ability to edit them (if required, although that's likely to give an auditor a conniption fit).
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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