CODEWITHSUNDEEP
vbaexcel
kdean693
kdean693

Reputation: 27

Excel: How to update multiple tables at the same time

I have these 2 tables in two different sheets in excel. They both have common tabs but the only one I am concerned about is the student ID. What I would like to do is make changes to the student ID in the first table and have it reflected in the second tables student ID for data consistency. I am working in excel 2010 and everything I have researched is for later versions of excel. I have tried the special link technique but because these are 2 tables it does not work. The special link option does not appear. I am not sure if I need a script to do this or if excel 2010 has a built in way to do this.

First Table:

Student ID  Last name   Initial Age Program
STF348-245  Another     L.       21 Drafting
STF348-246  Different   R.       19 Science
STF348-247  Name        G.       18 Arts
STF348-248  Going       L.       23 Nursing
STF348-249  Up          M.       37 Science
STF348-250  And         J.       20 Arts
STF348-251  Down        F.       26 Business
STF348-252  Different   S.       22 Arts
STF348-253  Different   W.       20 Nursing
STF348-254  Different   L.       19 Drafting

Second Table:

Student ID  Last name   Initial Age Program
STF348-245  Another      L.      21 Drafting
STF348-246  Different    R.      19 Science
STF348-247  Name         G.      18 Arts
STF348-248  Going        L.      23 Nursing
STF348-249  Up           M.      37 Science
STF348-250  And          J.      20 Arts
STF348-251  Down         F.      26 Business
STF348-252  Different    S.      22 Arts
STF348-253  Different    W.      20 Nursing
STF348-254  Different    L.      19 Drafting

Upvotes: 1

Views: 2653

Answers (1)

David Zemens
David Zemens

Reputation: 53623

With the exception of inserting or appending new rows, this can be accomplished using the VLOOKUP function only, assuming the Student ID field is a unique identifier.

In the "Program" field of Table2, put: 

=VLOOKUP([@[Student ID]],Table1,5,False))

Copy/Drag the formula down. Now any changes to Program on Table1 will be reflected in Table2.

Follow the same procedure for other columns, simply using the appropriate header name as the first argument to the function, and making sure to also change the column Index (5 in the above example).

NB: This assumes the "first" table is named "Table1" -- if not, modify the formula accordingly.

If you want to preserve the tables as strict duplicates of one another, including order, then you don't even need VLOOKUP. In Table2, just do:

Student ID             | Student Name             | Last Name
=Table1[@[Student ID]] | =Table1[@[Student Name]] | =Table1[@[Last Name]]

Upvotes: 1

Related Questions