Purchase Order Help

[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.

 

[Paul Falla]

Dear Aladd1n

This will be fairly simple to do, you will be glad to hear.
You will need to use the VLOOKUP function.
Now you can either hold the customer demographics in a
separate worksheet on each of the 5 workbooks, or you can
create a master index workbook that is stored separately.

Either way you will need some way of uniquely identifying
the customer, so that the lookup pulls the correct record.

If you are not familiar with using the Vlookup function,
please post back, and we will get you through it.

Hope this helps

Paul

 

[Freemini]

Another way is to set up a Master sheet on your template. This could
then contain the Name, address, lot# etc these would be held in column
A in Column B the actual data.

Select column A and B (just the rows being used)

From Insert choose Name and Create.

Check the box for Left column this will define the names.

go to the first PO sheet and then select all PO sheets using the shift
and pointing to the last tab.

On your first PO sheet enter "=Name" in the cell to hold the customers
name, repeat for all other details.

Select the first sheet tab with the shift button down to deselect all
the other sheets.

Now when you enter the details on the master sheet all the PO sheets
will automatically be updated.


HTH

Mike

 

[Aladd1n]

thanks for the quick responses, i will try both suggestions and let you
guys know if i cant get it.

aladdin