Pulling Data from an Excel Spreadsheet to Generate Worksheets?

[ctackett6407]

Greetings,

I'm not sure exactly where to start, but I have an idea of what I'm trying to do. I tried to think of a way to import all these excel files into an Access Database.. but I am not very skilled, I can use the out of the box stuff for Access, but can't seem to get it to do what I'm trying to get done.

I process 100's of orders a day. The way our process is right now, it is VERY redundant. (something that seems to only need to take 20 mins.. can take 7 hours, easy. Since we are manually entering all the data it leaves room for a LOT of human error)

My goal is to consolidate this into as few steps as possible.

I cannot bypass manually entering the order into our Great Plains software, but after I enter all the orders in the Great Plains software I can go in and export an excel spreadsheet that contains information that I can use in my 2nd purchase order I have to complete.

I have to make an excel spreadsheet for every purchase order I complete. I have to enter every Item Number, Style, PO#, Unit Cost, etc... and this is the part that takes hours.

Is there a way that Excel 2016 can pull all that data from another excel file that I export from Great Plains and generate worksheets and pull the data to fill into designated fields on the excel purchase order?

For example;

I enter 30 purchase orders into Microsoft Great Plains, The orders can range from 10 different items to 100 different items of various quantities...

When I'm done I generate a report on all the PO's that were created by me that day..

I get a file that looks something like this


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PO Number
[/TD]
[TD]PO Line Status
[/TD]
[TD]PO Type
[/TD]
[TD]Item Number
[/TD]
[TD]Item Description
[/TD]
[TD]Vender ID
[/TD]
[TD]Location Code
[/TD]
[TD]QTY
[/TD]
[TD]Unit Cost
[/TD]
[TD]Extended Cost
[/TD]
[TD]Document Status
[/TD]
[TD]PO Status
[/TD]
[/TR]
[TR]
[TD]PO1111
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11111
[/TD]
[TD]Item Desc
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]1999
[/TD]
[TD]$0.92
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important
[/TD]
[TD]Not Important
[/TD]
[/TR]
[TR]
[TD]PO1111
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11112
[/TD]
[TD]Something2
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]1233
[/TD]
[TD]$1.33
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important
[/TD]
[TD]Not Important
[/TD]
[/TR]
[TR]
[TD]PO1111
[/TD]
[TD]Released
[/TD]
[TD]Normal[/TD]
[TD]11113
[/TD]
[TD]Something3
[/TD]
[TD]1010
[/TD]
[TD]Main[/TD]
[TD]1236
[/TD]
[TD]$0.26
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important
[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1111
[/TD]
[TD]Released
[/TD]
[TD]Normal[/TD]
[TD]11114
[/TD]
[TD]Something4
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]134243
[/TD]
[TD]$0.88
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important
[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1112
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11115
[/TD]
[TD]Something5
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]1513
[/TD]
[TD]$1.99
[/TD]
[TD]AnotherNumberHere[/TD]
[TD]Not Important
[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1113
[/TD]
[TD]Released
[/TD]
[TD]Normal[/TD]
[TD]11116
[/TD]
[TD]Something6
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]153122
[/TD]
[TD]$2.88
[/TD]
[TD]AnotherNumberHere
[/TD]
[TD]Not Important[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1113
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11117
[/TD]
[TD]Something7
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]6364
[/TD]
[TD]$2.99
[/TD]
[TD]AnotherNumberHere[/TD]
[TD]Not Important
[/TD]
[TD]Not Important
[/TD]
[/TR]
[TR]
[TD]PO1114
[/TD]
[TD]Released
[/TD]
[TD]Normal
[/TD]
[TD]11119
[/TD]
[TD]Something8
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]464353
[/TD]
[TD]$2.00
[/TD]
[TD]AnotherNumberHere[/TD]
[TD]Not Important[/TD]
[TD]Not Important[/TD]
[/TR]
[TR]
[TD]PO1114
[/TD]
[TD]Released
[/TD]
[TD]Normal[/TD]
[TD]11118
[/TD]
[TD]Something9
[/TD]
[TD]1010
[/TD]
[TD]Main
[/TD]
[TD]3646
[/TD]
[TD]$1.99
[/TD]
[TD]AnotherNumberHere[/TD]
[TD]Not Important
[/TD]
[TD]Not Important
[/TD]
[/TR]
</tbody>[/TABLE]

I have an excel spreadsheet that I then have to transfer, manually, the information in bold into..

I'm still going to have to manually enter a few things in as the excel generated from Microsoft Great Plains doesn't show Style.. but.. If there was a way a worksheet could be generated from the excel for each of the "different" PO Numbers in the excel file (example above).. and then insert the data into fields into that PO.. automatically..

Like all the Line items from PO# 1111 ... would go to Worksheet PO# 1111 .. and then all the line items associated with that PO in the above Table would auto populate in the excel file.. and then all I had to do was go in and add the style .. or a couple other things.. but all the line items would already be done.

Any feedback would be great! I've been working on this for a week.. only had this job for a month, but I'm not keeping up to speed because it's like I'm doing the work over and over.. when it could be simplified.

 

[ctackett6407]

So on the above question I had asked,

I tried

=Master!$A$2:INDEX(Master!$A:$A,MATCH("*",Master!$A:$A,-1),1) as the formula for a defined field that I created.
I then used =defined_po for the dropdown list source, but sadly it didn't sort anything. I think this requires me to make a pivot table to use that code, but not really sure.

 

[vcoolio]

Hello CT,

Try the following link instead:-

https://www.dropbox.com/s/ne290v9wcqj9dr5/CTackett6407(copy&paste arrays,autofilter).xlsm?dl=0

I wasn't able to download from the link, but I tried the code and it had a never ending list.. in the right spot, but a never ending list of information. It also didn't populate in j3.

As per my last post, I simply typed the PO number in. It didn't self populate. So, to test it, just type in a PO number for now in J3 and you'll see that it works. To make this as simple as possible for you, create a drop down list in J3 with all the PO numbers.

One way to create a drop down in J3 is as follows:-

- In the Miscellaneous sheet that you created, pick any column and and name it say PO List then, from row2, list all the PO numbers. Next, high-light the whole list and go to the Insert Tab in the ribbon at the top of the sheet and select Table from the tables Group. A dialogue box should appear and, in the "Where is the data for your table" box, you should see the range for your list. Also, tick the "My table has headers" box and click OK. Your list should now appear as a two coloured table.
The next step is to select J3 in the Template sheet and then go to the Data tab in the ribbon. Select Data Validation from the Data Tools Group and from the drop down select Data Validation. In the Allow box, select list and in the Source box place the PO List range by selecting the whole PO List (select from row2 and drag down to the bottom of the list). Click OK. You'll now have a drop down in J3 listing all your PO numbers.
You can add or remove numbers from the table in future and the J3 drop down will update accordingly. You can also avoid duplicates this way.

I hope that this helps.

Cheerio,
vcoolio.

 

[TotallyConfused]

Hello Wcoolio

I don’t want to hijack this fascinating thread, but I wanted to say THANK YOU for a very detailed, accurate and easy to follow set of instructions on how to create a drop-down box. I’m referring to your post # 12. I’ve tried to do this, but had never been able to figure out the correct procedure. So many people when giving instructions, either assume the other person is already somewhat familiar with the procedure, or they will miss a step somewhere, which leaves the person trying to follow the instructions totally lost. For example, if we needed to click on ‘OK’, you said to do so. THANKS again and keep up the good work.

Now back to the thread and Ctrackett6407, please accept my apology for interrupting.

TotallyConfused

 

[vcoolio]

Glad to have been of help to you TotallyConfused!

Cheerio,
vcoolio.

 

[vcoolio]

Hello CT,

If the Drop Box link doesn't work (Drop Box has been giving me issues lately), try here again:-

http://ge.tt/3TlII7p2

Cheerio,
vcoolio.