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.

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

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

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.

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.

This can be done. If you follow the suggestions above about data setup then named ranges and lookups can be used.

HTH

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

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

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