Vlookup not updating automatically
However, you might want to keep your price list separate from the orders workbook, so the prices are easy to update.If your price list is in a different workbook, you can still use a VLOOKUP formula to pull the data, by referring to the external list.While the price list lookup table workbook is open, the VLOOKUP formula will show the workbook name and the referenced range address or range name.You can see the Price LU name and workbook information in the formula bar, in the screen shot below.PS The above code is for the people looking for a macro to solve the issue.
The Range A2: B10 will automatically update to become A2: C10, but the lookup column number will remain the same.
The COLUMN() formula will return the column number for the cell which it is referring to.
Here are some examples: This will return 26, because Z is the 26th column COLUMN only calculates based on the cell reference provided.
To refer to another workbook in a VLOOKUP formula, follow these steps.
In the screen shot below, the lookup table is in the Price workbook, on the Prices sheet, in a range named Price LU.