| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formulas Not Updating!!! | Excel Discussion (Misc queries) | |||
| Printing Excel Formulas without file paths updating | Excel Discussion (Misc queries) | |||
| Inserting Columns and Formulas updating | Excel Worksheet Functions | |||
| Copy worksheets with formulas between different workbooks | Excel Discussion (Misc queries) | |||
| Excel 2002 - copying formulas across worksheets | Excel Discussion (Misc queries) | |||
Linear Mode
