I have two workbooks. Workbook A has 1 worksheet, and two tables. Table1: Containing Student_ID, Student_Name, Student_Age Table2: Containing Student_ID, Student_TestResult
Workbook B has 1 workshet, with 1 table. Table3: Containing Student_ID, Student_TestResult I want to pull data from worksheet A and put in worksheet B, so that if I input Student_ID in workbook B, it will cross check with Student_ID in Workbook A, then match the test result.
This is what I have done so far: Solution 1: Directly use: =index(WorkbookA_Student_result,match(student_id,student_id range in A) It sometimes doesn't work because the formular will use table name and column instead of address like $a1:$b2 etc.. and that i don't know how many records I'll add in Workbook A! So I don't use this approach.
Solution 2: In workbook B, I insert a new worksheet named temp_data. In this worksheet, I mirror each and every cells in Workbook A. Examples, Table1 in WBook A is from $C$3:D$100, In temp_data, I'll have the links like this: =[WorkbokA.xlsx]Sheet1!$C3 to mirror everything... This is good, and it can meet my requirement. BUT I think i have done thing in a very "primitive way(?!)".
Solution3: I have learnt a new technique today, that is to create a connection from WbookB, and Excel will copy all things from workbook A, then paste it in Wbook B as a table. I don't have anything to complaints because it is quite good. BUT it has some cons. 1. It mirror EVERYthing from WBookA Sheet. This is okay, but seem to be annoying to me because: 2. I only one to get data from Table1 in WBookA, while this method pull both tables, and anything in WBookA sheet. Is there anyway i can pull only Table1? 3. Data in my WbookA is organized as (two) tables, starting from row 3. Row 1 and row 2 is for my introduction texts.. After copying to WorksheetB, it merge all as 1 table, and use the column lable (A,B,C, .. D,E..) as a table column! Question: Do you have any recommendation of "proper way" so that I can get data from WbookA, to work on it in WbookB? Thank you.