V lookup updating
Posted by CHRIS on February 19, 2001 9:13 AM
Ive got a Vlookup function running from 1 worksheet and ive got a table of data which is used in the range for the Vlookup. the table is in another worksheet. How would i automatically update the Vlookup Range Every time I add more data in the range via a macro. all the macro does is to copy and paste data thats in some cells into another part of the worksheet.
Posted by Aladin Akyurek on February 19, 2001 10:35 AM
May I suggest a different method: Since your lookup table changes dynamically ("by adding more data" to it), set up a dynamic range for your table.
Assuming that your table currently occupies the range A2:C7:
Step 1. Select this lookup range.
Step 2. Activate Insert,Define,Name. Type for Names in worbook:
DYNTABLE (or whatever name that is meaningful to you)
and enter the following formula for Refers to:
=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$C),3)
And click OK.
After all this you can use the name DYNTABLE in your VLOOKUP-formulas, and add more data to your table whenever needed.
Note that columns A to C should not contain any data other than your table.
Aladin
