Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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.

Check out our Excel Resources

Re: V lookup updating

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.