I am having trouble extracting data to a template from a closed master workbook without having to open then close the master workbook. The template is located in another
folder and will potentially be on another drive partition. I have tried indexing in the data with both excel files opened in the same window and in separate windows and it don't seem to make a difference.
The Master File in my example has 11 columns and 10 rows and is formatted as a table called "Data" on a tab also called "Data" in a workbook called "Reference 2017.xlsx."
The template is called "Worksheet 2017.xlsx." I index on the Part # and pull the other data in from there.
In the Reference 2017 Master, the parts numbers are in Column "A". This is the data table:
A B C D E F G H I J K
Part Number Description Group Attribute-1 Attribute-2 Attribute-3 Attribute-4 Attribute-5 Attribute-6 Special Notes Engine Model
289770 Widget 1 Bearing Flaws Fit Size Marks OD ID Shielding Measure Extension Model 1
289771 Widget 2 Bearing Flaws Fit Size Marks OD ID Shielding Measure Extension Model 1
312224 Widget 3 Gasket Visual Fit Bolt Alignment Thickness - - Stamped Correctly Model 1
541829-0002 Widget 4 Wheel Verification Notables - - - - Verify Clearances Model 2
3617026 Widget 5 Pulley Verification Notables - - - - - Model1
4071583 Widget 6 Line Visual Length Hole Size End Size Blockage Notables New Part Model 1
4072869 Widget 7 Bearing Flaws Fit Size Marks OD ID Shielding Inspect for Flat Spots Model 1
4527573 Widget 8 Wheel Verification Notables - - - - Check Against Specs Model 1
5067535 Widget 9 Line Visual Length Hole Size End Size Blockage Notables Do Pressure Test Model 1
This is the Worksheet:
Engine Type __________ =IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),11),"")
Part Number __________ (ENTER PART NUMBER HERE TO PULL THE REST OF THE DATA IN FROM MASTER LOG)
Part Description ______________________ =IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),2),"")
Special Notes _____________________________________ =IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),10),"")
Attribute ________________________________________ =IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),4),"")
Attribute ________________________________________=IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),5),"")
Attribute ________________________________________=IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),6),"")
Attribute ________________________________________=IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),7),"")
Attribute ________________________________________=IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),8),"")
Attribute ________________________________________=IFERROR(INDEX('REFERENCE 2017.xlsx'!DATA[#All],MATCH($E$5,'REFERENCE 2017.xlsx'!DATA[[#All],[Part Number ]],0),9),"")
When I open the template and enter a Part #, I get no data until I open the Reference 2017.xlsx file, then all of the data autofills. What am I doing wrong? Thanks.
Bookmarks