Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    5

    Unanswered: Purchase Order Questions

    Hello,

    I am a purchasing agent at a homebuilding company. I am creating a new purchasing system using Excel so over the next couple weeks you may see a bunch of questions from me. In all cases I have already searched the forum and have not found an answer.

    A little background: My company builds 5 different models. During the homebuilding process, I will be issuing approx 30 Purchase Orders to 30 different vendors for each house. I have set up 5 workbooks (for each of the designs) with 30 different worksheets in each workbook containing the purchase order information for each vendor.

    I would like to use these 5 workbooks as templates so that whenever we sell a model I just have to “save as” the corresponding template to a new filename and issue the POs.

    The question: I would like to eliminate the re-entry of customer data (i.e. Lot #, Address, Customer Name etc..) on each PO. Is there a way to create a master sheet or something in each of the workbooks containing this information that would automatically copy to the same cells in each of the other 30 sheets in the workbook? Ideally, once I saved the template to the new file, I would only have to change the customer data once and that’s it.

    Thanks in advance for your help. This forum is one of the best I’ve seen.

    Aladdin Nassar
    Meadowbrook Homes, Inc.

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Sounds to me like this is something better of in a DB (access) than in a spreadsheet, geezz.....30 worksheets... I say GO DB...

    Regards

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi Aladdin

    Probably the easiest way to go about this is have your master sheet with the information on it and on each worksheet cell where you want information just type

    =

    then click on your mastersheet then on the cell which holds the information which you want to populate the cell with.

    if you create a blank template with all these links in Excel will automatically populate the cells required when information is then input into the system.

    but i agree with namliam that an actual database is the better option to choose

    David

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091

    Re: Purchase Order Questions

    Welcome to the Excel portion of the board!

    30 worksheets is not bad for Excel; I know someone who has 180 worksheets in each workbook. The key issue is the memory available.

    As for whether to use Excel or database, much depends on what direction you want to go and how much expansion you want. Will there be consolidation of data, archival type retrieval work? etc.

    If you set up the data properly in Excel the sheets can be rather easily imported into MS Access, if needed. So, the key will be to setup the data properly. I would suggest thinking about this in terms of tables as set up in a database, but in Excel. If the data is not setup properly you will lose much of the ability of Excel to assist you in your work. That means that you might have a data worksheet, and then the 30 other worksheets that feed off that data; those 30 can then focus on presentation and format.

    Originally posted by Aladd1n

    A little background: My company builds 5 different models. During the homebuilding process, I will be issuing approx 30 Purchase Orders to 30 different vendors for each house. I have set up 5 workbooks (for each of the designs) with 30 different worksheets in each workbook containing the purchase order information for each vendor.

    I would like to use these 5 workbooks as templates so that whenever we sell a model I just have to “save as” the corresponding template to a new filename and issue the POs.
    Be sure that when you have what you want for the five templates to save them .xlt files, then when you select the template to open it will open as unsaved, and you won't lose the original file.

    The question: I would like to eliminate the re-entry of customer data (i.e. Lot #, Address, Customer Name etc..) on each PO. Is there a way to create a master sheet or something in each of the workbooks containing this information that would automatically copy to the same cells in each of the other 30 sheets in the workbook? Ideally, once I saved the template to the new file, I would only have to change the customer data once and that’s it.
    This can be done. If you follow the suggestions above about data setup then named ranges and lookups can be used.

    HTH
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Sep 2003
    Location
    Cincinnati, Oh USA
    Posts
    203

    Re: Purchase Order Questions

    Creating a purchase order system is always a challenge, but I think
    you'd be better off using a database rather than Excel. Not to demean XL, it's a great application, but your ability to retain history and analyze
    purchases could be difficult. If your intention is to not maintain any historical records of purchases, by all means use Excel. But I'd give a hard look at Access or FileMaker to build this system in.
    Good Luck


    Originally posted by Aladd1n
    Hello,

    I am a purchasing agent at a homebuilding company. I am creating a new purchasing system using Excel so over the next couple weeks you may see a bunch of questions from me. In all cases I have already searched the forum and have not found an answer.

    A little background: My company builds 5 different models. During the homebuilding process, I will be issuing approx 30 Purchase Orders to 30 different vendors for each house. I have set up 5 workbooks (for each of the designs) with 30 different worksheets in each workbook containing the purchase order information for each vendor.

    I would like to use these 5 workbooks as templates so that whenever we sell a model I just have to “save as” the corresponding template to a new filename and issue the POs.

    The question: I would like to eliminate the re-entry of customer data (i.e. Lot #, Address, Customer Name etc..) on each PO. Is there a way to create a master sheet or something in each of the workbooks containing this information that would automatically copy to the same cells in each of the other 30 sheets in the workbook? Ideally, once I saved the template to the new file, I would only have to change the customer data once and that’s it.

    Thanks in advance for your help. This forum is one of the best I’ve seen.

    Aladdin Nassar
    Meadowbrook Homes, Inc.
    Rick Knight
    KnightShift Office Solutions and Horse Breaking
    VB, VBA, FileMaker, Access Solutions, Web Solutions

  6. #6
    Join Date
    Jan 2004
    Posts
    5
    thanks for the quick responses,

    ideally i would like to set up a database such as access but it seems like it would be much easier, at least at set up, to use excel. i am afraid that if i try to implement an access database, it would be one of those projects that never gets done.

    let me know what you think

    aladdin

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    having repetative data
    Models and stuff which are all repetative as well....

    I would still say (or even more so now) GO DB.

    This is what a real relational DB is all about, taking away double information and stuff. This will also allow you to not end up with countless spreadsheets but just 1 db for all models. You can then simply select what project you want or query how many times have we done this or that... and what is the status of all ongoing projects ....

    My $0.02, Go DB....

    Regards

Posting Permissions

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