Hi Sandra,

As you are using a macro to insert the column you can initially name the
range B:C on the worksheet (See 'define name' in the worksheet help) and then
use the named range in the formula like the following (I have named the range
'MyLookUpRng':-

=VLOOKUP(B1,MyLookUpRng,2,FALSE)

Then in the macro after inserting the column rename columns B:C to the named
range like the following (Note C2:C3 is columns 2 and 3):-

Sheets("Data").Columns("B:B").Insert Shift:=xlToRight

ActiveWorkbook.Names.Add Name:="MyLookUpRng", _
RefersToR1C1:="=Data!C2:C3"

--
Regards,

OssieMac


"Sandra" wrote:

Spreadsheet has formulas that pick up values from data tab. Macro inserts a
new column B in the data tab. When it does that, the formulas move from B:C
to C:D. The formula is in a vlookup. How do I get the formula to stay as
B:C. I tried INDIRECT but it does not work in VLOOKUP. Any ideas?