Linking Certain Data from a "Source" Workbook to a Separate Workbook

[Bryan1]

I have a "source" workbook that contains multiple columns with headers accompanied by several rows of data. One of the headers on the source workbook is Order Number. I would like to create a separate workbook for each order in the source workbook. For example, I have Order Numbers 1, 2, & 3 in source workbook. I'm trying to pull all the information (15 columns worth of headers and multiple rows of data) pertaining to Order 1 to its own workbook, and so forth.

I started out using the IF formula, but quickly realized there were flaws in this technique. When pulling all of Order 1 data from the source workbook, the IF formula left blank rows where Order 2 was originally on the source workbook instead of bypassing Order 2 all together. Plus each cell has to have its own unique formula.

I also did research on an external reference, but I've had problems getting it to do what I want it to do.

My goal is to find the best way for each "order" workbook to update when new data has been added to the source workbook. Any help would be greatly appreciated. Thanks.

 

[Chihiro]

Leverage MS Query or PowerQuery depending on your Excel version.

Based on Excel 2010
MS Query
1. Go to Data ribbon tool, hit from "From Other Sources"->"From Microsoft Query"
2. Select "Excel Files" in Databases pane. Hit Ok.
3. Browse and find the source workbook. Hit OK.
4. Select worksheet and move to right pane. Hit next until you see "What would you like to do next?"
5. Select "View data or edit query in Microsoft Query". Hit finish.
6. Hit add criteria. Choose field that contains Order number. With operator "equals" and type appropriate value. Hit "Add".
7. Go to File menu in MS Query and choose "Return Data to MS Excel". Follow on screen instruction.

Note: If file location changes, you may need to edit Command text in Connection->Property->Definition

PowerQuery:
1. Go to PowerQuery ribbon and choose "From File"->"From Excel"
2. Browse and find source workbook. Hit Ok.
3. Choose Table/Sheet to import. Hit Edit.
4. In the editor, Filter on the column containing Order Numbers. Set filter to equal the Order Number.
5. Click "Close and load" and choose "Close and Load To". Follow on screen instruction and load table to sheet.

You may need to edit "Source" if workbook location changes.

 

[Bryan1]

Thanks for the quick response.

I followed the steps listed above for MS Query, but unfortunately, I did not get too far.

On step 3, I selected the source workbook and the following message displayed: "This data source contains no visible tables." Once I hit OK on the message, the Query Wizard - Choose Columns box appeared. All panes were blank. This is where I get stuck. Not sure what to do next.

As for the PowerQuery option, I do not have "From File" or "From Excel" as options.

I'm running Excel 2013. Any suggestions for getting past Step 3?

 

[Chihiro]

In the Query Wizard, if you don't see any data to pull. Try clicking on "Options" and check "System Tables" and hit Ok.

This should fix the issue most of the time.

 

[Bryan1]

Yes, this helped. I think I'm good to go now. I sure appreciate your help and prompt responses. Thanks again.