Pulling Data from an Excel Spreadsheet to Generate Worksheets?

ctackett6407

Board Regular
Joined
Mar 18, 2018
Messages
64
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


PO Number
PO Line Status
PO Type
Item Number
Item Description
Vender ID
Location Code
QTY
Unit Cost
Extended Cost
Document Status
PO Status
PO1111
Released
Normal
11111
Item Desc
1010
Main
1999
$0.92
AnotherNumberHere
Not Important
Not Important
PO1111
Released
Normal
11112
Something2
1010
Main
1233
$1.33
AnotherNumberHere
Not Important
Not Important
PO1111
Released
Normal11113
Something3
1010
Main1236
$0.26
AnotherNumberHere
Not Important
Not Important
PO1111
Released
Normal11114
Something4
1010
Main
134243
$0.88
AnotherNumberHere
Not Important
Not Important
PO1112
Released
Normal
11115
Something5
1010
Main
1513
$1.99
AnotherNumberHereNot Important
Not Important
PO1113
Released
Normal11116
Something6
1010
Main
153122
$2.88
AnotherNumberHere
Not ImportantNot Important
PO1113
Released
Normal
11117
Something7
1010
Main
6364
$2.99
AnotherNumberHereNot Important
Not Important
PO1114
Released
Normal
11119
Something8
1010
Main
464353
$2.00
AnotherNumberHereNot ImportantNot Important
PO1114
Released
Normal11118
Something9
1010
Main
3646
$1.99
AnotherNumberHereNot Important
Not Important

<tbody>
</tbody>

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

Board Regular
Joined
Mar 18, 2018
Messages
64
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,026
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

Board Regular
Joined
May 4, 2017
Messages
241
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,861
Messages
5,574,708
Members
412,613
Latest member
EFRATA
Top