Dynamic Name Ranges and transferring data to new sheet

Good Evening Everyone,

I am still learning all I can of Excel but I am still running into problems and need some programming help.

I have an Orders database that documents several items that are sold. Upon hitting a create order button the data is transferred to a datasheet with a running total of all sales. I am using a dynamic name range to capture all lines of data to transfer to the total sheet. The problem I am having is that I need to generate a production report from this same date but not necessarily in the same order as on the order sheet.

I understand that column names should be the same when transferring (or am I wrong) but the production report has additional data that is not a part of the main product input area on the order form. Any ideas on how I should tackle this?

Thanks,

Jeff

Template-Order_Matic 6 with Production Report_2.xlsm

Here is what I have. I need to populate the production report with the data from the orders sheet.

Jeff

No password. Go into VBA editor, protect_unprotect module, and run the unprotect macro. I will log on later to address your other questions. What you see on the production report is what I need from the orders form. I will explain it in greater detail when I get home.

On the Orders sheet there is 4 hidden columns (C thru F) that are transferred to the All Orders sheet.

OK, all the data needed for the Production Report is NOT on the All Orders sheet. As seen on the Orders form I have a date ordered with our Order # as well as a Due Date and the Customer's PO #.
These last two items are not saved to the All Orders sheet and is not needed for our order tracking.

To go over each column on the Production Report
Due = Due Date from Order Sheet
Customer = Company (column F) from Order Sheet
Contract/Order = PO Order # from Order sheet
NSN/Code = Code from Order sheet
Part Number = Description from Order Sheet
Machine Shop = just a check block that can be set on the Production Sheet
Quantity Ordered = Quantity from Order Sheet
Quantity Shipped = May come from Invoice workbook in future but for now will be filled out on Production Report itself.
Quantity Open = Formula based on ordered and shipped.
Unit Cost = Unit price from Order Sheet
Value = Total from Order Sheet
Inspect and notes are filled out on the Production Report

Something I was thinking about. What if I added two more hidden cells to pull the due date and customer po # like i am doing with the company and order date then then transfer that to the all orders sheet. would it then be easier to populate the production report with each item ordered?

I do appreciate the help. Please let me know if you have any other questions. The VBA code protects the sheet after each order is processed. For now I just go into the Protect_Unprotect module and run the unprotect macro to unlock everything.

Jeff

Just an update. I was able to add the due date and customer po # to the all orders sheet by adding two more hidden columns to capture the data like i am the customer and date. Now do I copy the data to the production report when i copy the data to the all orders sheet or do I have to do it separately?

Jeff

It is just a copy of the data. We print out the report to have a paper copy to plan our production and schedule our deliveries. All the manipulation of the data is done elsewhere in the workbook.

Jeff

OK, so how do I set up the name range to grab the data in the order I need to place it on the production report? As you can see I use Orders to select all the data in the row from the Orders sheet to place on All Orders. As the Production Report is slightly different how do I grab the data in the correct order?

Jeff

Thank you for your help. It seems to be working well.

Jeff

Thank you for your help. It seems to be working well.

Jeff

Online-PC-Learning-Invoice-Generator Tutorial 6 test to pull data from orders.xlsm

I have been trying to figure out how to pull the Order data back into the Invoice program to finish this project.

On the Invoice sheet in the Invoice Program I want to pull the ordered data from the AllOrders sheet in the Orders Program when the Order No. in placed on the Invoice Sheet. I want to pull the customer name(the address information is being pulled via lookup from the Orders Program), as well as Quantity through Total from the AllOrders sheet to populate the Invoice sheet.

I am trying not to have to duplicate data between both databases. I plan on deleting Products and Customers from the Invoice program and just have it in the Orders program. I really wanted to merge both programs together but that proved troublesome so I am trying to just link them together. I have read several articles on this type of data lookup but it just keeps confusing me. If you have any questions please let me know.

Jeff