Formulas across worksheets are not updating
 

--------------------------------------------------------------------------------

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.


--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile: http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

Formulas across worksheets are not updating
 

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?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"surg4u1975" wrote
in message ...

--------------------------------------------------------------------------
------

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.


--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile:
http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

Formulas across worksheets are not updating
 

Inserting between T and X.


--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile: http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

Formulas across worksheets are not updating
 

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)

surg4u1975 wrote:

Inserting between T and X.

--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile: http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

--

Dave Peterson

Formulas across worksheets are not updating
 

ps, you could use this convulted formula, too:

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

surg4u1975 wrote:

Inserting between T and X.

--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile: http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

--

Dave Peterson

Formulas across worksheets are not updating
 

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" wrote
in message ...

Inserting between T and X.


--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile:
http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

Formulas across worksheets are not updating
 

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.


--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile: http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

Formulas across worksheets are not updating
 

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"surg4u1975" wrote
in message ...

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.


--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile:
http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

Formulas across worksheets are not updating
 

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?


--
surg4u1975
------------------------------------------------------------------------
surg4u1975's Profile: http://www.excelforum.com/member.php...o&userid=28718
View this thread: http://www.excelforum.com/showthread...hreadid=484108

Formulas across worksheets are not updating
 

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

surg4u1975 wrote:
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?