Assume a two column database in range B3:C11 of sheet1 of Book1.xlsx. Headings are in B3:C3. B4:B11 has names and C4:C11 has numbers. Save this file on the desktop.
Open another workbook (Book2.xlsx) and type any name in cell B6 of sheet1 (the name should be one of the names mentioned in range B4:B11 of sheet1 of Book1.xlsx). Save this file on the desktop.
With both workbooks open, enter the following formula in cell C6 of sheet1 of Book2.xlsx
=VLOOKUP(B6,[book1.xlsx]Sheet1!$B$4:$C$11,2,0)
The result would be correct. Now enter a text value in cell B12 of sheet1 of Book1.xlsx and a corresponding number in cell C12. Enter the same name in cell B6 (overwrite the existing value) of sheet1 of Book2.xlsx.
The result will be #N/A in cell C12 because the range in the VLOOKUP() function is still till row 11 only. While the easiest way to get the answer would be to edit C11 to C12 in the formula, this would be a manual process for every occasion when data expands in sheet1 of Book1.xlsx. To make the range in the VLOOKUP() auto expand for new entries added in sheet1 of Book1.xlsx, you may try the following:
1. Delete the data in range B12:C12 of sheet1 of Book1.xlsx
2. Select B3:C11 and press Ctrl+L or Ctrl+T (Insert > Table) to convert the range to a Table
3. In cell B6 of sheet1 of Book2.xlsx, enter any name from range B4:B11 of sheet1 of Book1.xlsx
4. In cell C6 of sheet1 of Book2.xlsx, rewrite the VLOOKUP() function. It will now look like this
=VLOOKUP(B6,book1.xlsx!Table1[#Data],2,0)
The benefit of converting the source data range into a Table is that for new rows added to the source data table, the range in the VLOOKUP() function will keep expanding. Therefore, you will not need to manually edit the range in the VLOOKUP() function.
However, now try this to see where the Table feature fails:
1. Save and close Book1.xlsx
2. Press F2 on cell C6 of sheet1 of Book2.xlsx and then Enter
The result will be #REF!. This happens because named ranged do not work across workbooks – when converting the source data range into a table, each column inherited a name from the column heading (book1.xlsx!Table1[#Data]). When you open Book1.xlsx, the #REF! error will be replaced with the actual value.
To make the formula work when the source workbook is closed, try this
1. In any of the two Excel workbooks, go to File > Options > Formula and uncheck the box for “Use Table names in formulas” (available under “Working with Formulas”). This path is for Excel 2010.
2. In cell D6 of sheet1 of Book2.xlsx, rewrite the VLOOKUP() function. It will now look like this
=VLOOKUP(B6,[book1.xlsx]Sheet1!$B$4:$C$12,2,0)
Close Book1.xlsx. When you press F2 and Enter on C6 and D6 of sheet1 of Book2.xlsx, C6 will return the #REF! error while cell D6 would show the result just fine.
Therefore, when working with Excel tables across workbooks, ensure that the setting under Excel Options is deactivated.
Force Excel tables to work across workbooks
{ 5 Comments }