Formulas not updating

[surg4u1975]

I am using VLOOKUP to pull data from one sheet to another.

When I insert a column in the sheet referenced in the VLOOKUP formula
the formula is not auto updating. Is there a preference setting that I
have erroneously changed?

i.e. =VLOOKUP(A3,'Sheet1'!$T:$X,3,FALSE) and the 3 should change to a
4 if I insert a column in column 1 through 3 in Sheet 1.

 

[Joseph Mc Daid]

Surg4u1975

Perhaps this might help, its not the most elegant, but it should work.
Ensure the "COLUMN(Sheet1!$V$1)" is set to the target column.

=VLOOKUP(A3,Sheet1!$T:$X,-COLUMN(Sheet1!$T$1)+COLUMN(Sheet1!$V$1)+1,FALSE)

HTH

Joseph Mc Daid

 

[surg4u1975]

That works, however, EXCEL is supposed to automatically update the
formula if I insert columns within the columns referenced in the
formula. I just cannot figure out what I have changed that eliminated
that ability.

Thanks for the correction though. I will use that in the meantime.

 

[Joseph Mc Daid]

surg4u197,

This is how excel behaves it is not just your copy.

Excel has no idea that the 3 in your formula should be tied to the
number of columns between column X and V. What your formula tells excel
is to return the 3rd column in the given range and this is what it
does. The work around i provided uses a formula that is sensitive to
the number of columns between column X and column V to adjust the
column offset to always resolve to the correct column.

In summary Excel only updates range references (A1:F6) for insertions
and deletions and not user entered assumptions (eg The number 3).

HTH

Joseph Mc Daid