Formulas across worksheets are 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.

 

[RagDyer]

When you say:
<<<"I insert a column in column 1 through 3 in Sheet 1">>>
Exactly what columns are you talking about?

Do you mean inserting columns before or after Column A, B, or C, or do you
mean inserting columns between Columns T and X?

 

[surg4u1975]

Inserting between T and X

 

[Dave Peterson]

Excel won't change that 3.

One way around it if you have nice unique headers is to use =index(match()).

Debra Dalgleish explains it at:
http://contextures.com/xlFunctions03.html
particularly:
http://contextures.com/xlFunctions03.html#IndexMatch2
(the second example)

 

[Dave Peterson]

ps, you could use this convulted formula, too:

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

 

[RagDyer]

Then try this formula:

=VLOOKUP(A3,Sheet1!$T:$X,COLUMNS(T:X)-2,FALSE)

Each time you insert, the formula will change to reflect the *new* range.

If you want to return different columns after an insertion, play with the
"-2".
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[surg4u1975]

I appreciate all the different ways to get this to work, and all that I
have tried work. However, has anyone got an idea about why EXCEL is
not automatically updating the formula in the spreadsheet.

 

[RagDyer]

XL automatically updates and revises address references in formulas when
they are moved or copied.

The column index in the Vlookup formula is *not* an address reference, and
therefore, is not revised by XL.

 

[surg4u1975]

I just reloaded EXCEL using the repair function to no avail. This was
not an issue two days ago, and I think my company just loaded the
newest round of microsoft updates yesterday.

Anyone heard any issues with the latest round of updates?

 

[Rowan Drummond]

You got this reply form Joseph McDaid over an hour ago in your post in
microsoft.public.excel. If you had no multi-posted this you may have
saved yourself the trouble of re-installing excel:

Quoted post:

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

Regards
Rowan